{"text_format":"1","workbook":"xlsx","n_sheets":30,"sheets":["Topics","Formula Notes","Golden Rule","Formula Examples","Formula Examples (an)","Errors","Errors (an)","Homework ==>>","HW(1)","HW(1an)","HW(2)","HW(2an)","HW(3)","HW(3an)","HW(4)","HW(4an)","HW(5)","HW(5an)","HW(6)","HW(6an)","HW(7)","HW(7an)","HW(8)","HW(8an)","HW(9)","HW(9an)","HW(10)","HW(10an)","HW(11)","HW(11an)"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":1,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Topics","index":0,"n_rows":18,"n_cols":2,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Highline Excel 2016 Class 02: Excel Fundamentals: Formulas, Formula Types, Formula Elements, Functions & Excel's Golden Rule"]
[]
["Topics:"]
[1,"Excel’s Golden Rule"]
[2,"Formula Types, Formula Elements, How Formulas Calculate, Formula Errors"]
[3,"Types of Formulas in Excel"]
[4,"Types of Formula Elements"]
[5,"Examples of Functions"]
[6,"COUNTIFS, SUMIFS, AVERAGEIFS and similar functions"]
[7,"VLOOKUP function:"]
[8,"IF Function"]
[9,"Types of Calculations"]
[10,"LARGE and SMALL functions"]
[11,"How Formulas Calculate (Order of Precedence)"]
[12,"4 Steps to Finding Errors  in Formulas"]
[13,"Types of Error Messages"]
[14,"When to use ROUND function"]
[15,"3 ways to enter cell references into formulas:"]

{"sheet":"Formula Notes","index":1,"n_rows":46,"n_cols":13,"rows_kept":46,"cut":false,"hidden":false,"merged":[]}
["Types of Formulas in Excel:"]
["1) Number formulas that deliver a single number answers such as a tax deduction amount or a budgetary expense amount."]
["2) Text formulas deliver a text item such as a name or category."]
["3) Logical formulas (Boolean Formulas) deliver a TRUE or FALSE."]
["4) Lookup formulas lookup a particular item in a table and return an item such as a phone number or tax rate."]
["5) Aggregate formulas are formulas that take two or more items and calculate a single answer."]
["6) Array formulas are formulas that operate on an array of items rather than single items (“array operation”) and which deliver an array of items as a result of the array operation."]
[]
["The types of Formula Elements that are allowed in formulas are:"]
["1) Equal sign."]
["2) Cell references (also defined names, sheet references, workbook references."]
["3) Table Formula Nomenclature (Structured References)."]
["4) Math operators ==>",null,null,null,null,null,null,null,null,null,"Math Operators."]
["5) Numbers (if they won’t change)",null,null,null,null,null,null,null,null,null,"+","Adding.","Remember Order of Operations in Math:"]
["6) Built-in Functions.",null,null,null,null,null,null,null,null,null,"-","Subtracting or Negation.","1) Parentheses"]
["7) Function argument elements, such as FALSE or 0 in VLOOKUP function to indicate Exact Match.",null,null,null,null,null,null,null,null,null,"*","Multiplying.","2) Exponents"]
["8) Comparative operators ==>",null,null,null,null,null,null,null,null,null,"/","Dividing.","3) Multiply & Divide, Left to Right"]
["9) Join operator: Ampersand (&).",null,null,null,null,null,null,null,null,null,"^","Raising to an exponent.","4) Adding and Subtracting, Left to Right"]
["10) Text within quotation marks.",null,null,null,null,null,null,null,null,null,"( )","Parentheses."]
["11) Array constants."]
[null,null,null,null,null,null,null,null,null,null,"Comparative Operators."]
[null,null,null,null,null,null,null,null,null,null,"=","Equal: are two things equal?"]
[null,null,null,null,null,null,null,null,null,null,"<>","Not: are two things not equal? Type less than symbol, then greater than symbol."]
[null,null,null,null,null,null,null,null,null,null,">","Greater than: is the thing on the left greater than the thing on the right?"]
[null,null,null,null,null,null,null,null,null,null,">=","Greater than or equal to: is the thing on the left greater than or equal to the thing on the right?"]
[null,null,null,null,null,null,null,null,null,null,"<","Less than: is the thing on the left less than the thing on the right?"]
[null,null,null,null,null,null,null,null,null,null,"<=","Less than or equal to: is the thing on the left less than or equal to the thing on the right?"]
[]
[null,null,null,null,null,null,null,null,null,null,"How Formulas Calculate: Order of Precedence in Excel."]
[null,null,null,null,null,null,null,null,null,null,1,"Parenthesis ( )"]
[null,null,null,null,null,null,null,null,null,null,2,"Reference Operators: colon, space, comma"]
[null,null,null,null,null,null,null,null,null,null,null,"Example of colon in range of cells: =SUM(A1:A4)"]
[null,null,null,null,null,null,null,null,null,null,null,"Example of intersection operator: =E12:G12 F10:F15 (retrieve what is in F12)"]
[null,null,null,null,null,null,null,null,null,null,null,"Example of comma (union): =SUM(E10:G10,E14:G14)"]
[null,null,null,null,null,null,null,null,null,null,3,"Negation (-)"]
[null,null,null,null,null,null,null,null,null,null,null,"Example: = -2^4 = 16"]
[null,null,null,null,null,null,null,null,null,null,null,"Example: = -(2^4) = -16"]
[null,null,null,null,null,null,null,null,null,null,null,"Example: --2+1 = 3"]
[null,null,null,null,null,null,null,null,null,null,4,"Converts % (1% to .01)"]
[null,null,null,null,null,null,null,null,null,null,5,"Exponents (^)"]
[null,null,null,null,null,null,null,null,null,null,null,"Example: 4^(1/2) = 2"]
[null,null,null,null,null,null,null,null,null,null,null,"Example: 3^2 = 9"]
[null,null,null,null,null,null,null,null,null,null,6,"Multiplication (*) and Division (/), left to right"]
[null,null,null,null,null,null,null,null,null,null,7,"Adding (+) and Subtracting (-), left to right"]
[null,null,null,null,null,null,null,null,null,null,8,"Ampersand (&)"]
[null,null,null,null,null,null,null,null,null,null,9,"Comparative symbols: =, <>, >=, <=, <, >"]

{"sheet":"Golden Rule","index":2,"n_rows":15,"n_cols":6,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Excel’s Golden Rule:"]
["1) If a formula input can change, put it into a cell and refer to it in the formula with a cell reference."]
["2) If a formula input will not change, you can type it into a formula."]
["3) ALWAYS Label Your Formula Inputs!!!"]
[]
["Goal: Calculate the Deduction at the 7.65% Tax Rate"]
[]
["Efficient:",null,null,null,null,"Why?"]
["Gross","Deduction",null,"Tax Rate",null,"1) It provides good documentation of the spreadsheet solution or spreadsheet model:"]
[1589.75,{"$f":"ROUND(A10*D10,2)","v":121.62},null,7.6499999999999999E-2,null,"2) Easy to Update the spreadsheet solution or spreadsheet model"]
[null,null,null,null,null,"3) Violating Excel’s Golden Rule by “Hard Coding” leads to errors:"]
["NO:",null,null,null,null,"Research has shown that a common error in spreadsheets is “Hard Coding”:"]
["Not Efficient:",null,null,null,null,"http://www.strategy-at-risk.com/2009/03/03/the-risk-of-spreadsheet-errors/"]
["Gross","Deduction"]
[1589.75,{"$f":"ROUND(A15*0.0765,2)","v":121.62}]

{"sheet":"Formula Examples","index":3,"n_rows":197,"n_cols":9,"rows_kept":197,"cut":false,"hidden":false,"merged":[]}
["Ex 1","Goal: Calculate Monthly Allocation."]
[null,"Type of Formula: Number Formula."]
[null,"Formula Elements: Equal sign, cell reference,"]
[null,"math operator, number."]
[]
[null,"Annual Insurance",13500]
[null,"Monthly Allocation"]
[]
["Ex 2","Goal: Calculate deduction for each employee."]
[null,"Type of Formula: Number Formula."]
[null,"Formula Elements: Equal sign, cell reference,"]
[]
[]
[null,"Employee","Gross Pay","Deduction",null,"TaxRate"]
[null,"Sioux",2830.34,null,null,0.14000000000000001]
[null,"Chin",2239.9299999999998]
[null,"Tyrone",2953.98]
[null,"Gigi",2926.74]
[]
[null,"Goal: Calculate dynamic label for counting above the hurdle."]
[null,"Type of Formula: Text Formula."]
["Ex 3","Formula Elements: Equal sign, Text in Double Quotes, Join Symbol (&), Cell Reference"]
["Ex 4","Formula Elements: Equal sign, Text in Double Quotes, Join Symbol (&), Cell Reference, Built-in Function DOLLAR"]
[]
[null,"Goal:  Calculate number of sales above hurdle."]
[null,"Type of Formula: Number Formula."]
["Ex 5","Formula Elements: Equal sign, Built-in Function, Table Formula Nomenclature (Structured References), Cell Reference."]
["Ex 6","Formula Elements: Equal sign, Built-in Function, Table Formula Nomenclature, Comparative Operator in Double Quotes, Join Symbol (Ampersand), Cell Reference."]
[]
[null,"Product","Sales",null,"Hurdle for Counting",">=1000"]
[null,"Carlota",1144,null,null,null,null,"**This is also an Aggregate Calculation made with a single criterion or condition."]
[null,"Carlota",338,null,"Ex 3","Ex 5"]
[null,"Quad",1033]
[null,"Quad",832,null,"Hurdle for Counting",1000,null,"**When you join Comparative"]
[null,"Tri Fly",1041,null,null,null,null,"Operator to Cell Reference, you"]
[null,"Quad",955,null,"Ex 4","Ex 6",null,"must put Comparative Operator"]
[null,"Tri Fly",587,null,null,null,null,"in Double Quotes."]
[null,"Quad",1190]
[null,"Tri Fly",945]
[null,"Quad",973]
[null,"Tri Fly",225]
[]
[]
[]
["Ex 7","Goal: Lookup Category to specify the type of sale it is: Below Par, Par, Excellent."]
[null,"Type of Formula: Lookup Formula. Text Formula."]
[null,"Formula Elements: Equal sign, Built-in Function, Cell Reference, Array Constant,"]
[null,"Function argument elements."]
[]
[null,"Sales Rep","Sales","Category",null,"Sales","Category",null,"VLOOKUP function:"]
[null,"Sioux",2935.52,null,null,0,"Below Par",null,"1) Looks at an item"]
[null,"Tyrone",6825.01,null,null,3000,"Par",null,"2) Tries to find a match in the first column of the lookup table"]
[null,"Chin",5404.56,null,null,6000,"Excellent",null,"Exact Match: tries to find exact match"]
[null,"Gigi",2470.35,null,null,null,null,null,"Approximate Match: bumps into first biggest item and jumps back one row"]
[null,"Sioux",5246.69,null,null,null,null,null,"3) retrieves something from a specified column"]
[null,"Tyrone",6768.17,null,null,null,null,null,"4) brings it back the  cell."]
[null,"Gigi",6664.08]
[null,"Tyrone",3513.37,null,null,null,null,null,"VLOOKUP: =VLOOKUP( lookup_value , table_array , col_index_num , [range_lookup] )"]
[null,"Gigi",4879.75,null,null,null,null,null,"lookup_value = thing you look at BEFORE you go over to the table."]
[null,"Chin",2525.66,null,null,null,null,null,"table_array = vertical table = VLOOKUP table"]
[null,null,null,null,null,null,null,null,"col_index_num = which column in the table has the thing you want to go and get and bring back to the cell."]
[null,null,null,null,null,null,null,null,"[range_lookup] = Exact Match = FALSE or 0. Approximate Match = TRUE or 1 or omitted."]
["Ex 8","Goal: Determine bonus amount for each employee."]
[null,"Type of Formula: Number Formula."]
[null,"Formula Elements: Equal Sign, Built-in Function, Relative Cell Reference, Comparative Operator,"]
[null,"Absolute Cell Reference, Number typed into formula."]
[]
[null,"Sales Rep","Sales","Bonus",null,"Hurdle:",3500,null,"IF function:"]
[null,"Sioux",2935.52,null,null,"Bonus:",75,null,"Use IF function to put one of two things into a cell or formulas."]
[null,"Tyrone",6825.01,null,null,null,null,null,"logical_test argument gets the Logical Formula that comes out to be TRUE or FALSE."]
[null,"Chin",5404.56,null,null,null,null,null,"value_if_true argument is what will go in cell if Logical Formula gets a TRUE."]
[null,"Gigi",2470.35,null,null,null,null,null,"value_if_false argument is what will go in cell if Logical Formula gets a FALSE."]
[null,"SheliaDawn",3500]
[]
["Ex 9","Goal: Determine If Debits = Credits"]
[null,"Type of Formula: Logical Formula."]
[null,"Formula Elements: Equal sign, Cell Reference,"]
[null,"Equal sign (as Comparative Operator), Cell Reference"]
[]
[null,"Debit (DR)","Credit (CR)",null,"In Balance?"]
[null,35.74,35.74]
[null,73.61,73.61]
[null,113.08,113.08]
[null,100.49,100.5]
[null,17.7,17.7]
[null,107.38,107.38]
[null,{"$f":"SUM(B81:B86)","v":448},448.01]
[]
[]
["Ex 10","Goal: Add Calls.",null,null,null,"Aggregate Calculations:"]
[null,"Type of Formula: Aggregate Formula. Number Formula.",null,null,null,"Calculations that take two or more items and"]
[null,"Formula Elements: Equal sign, Built-in Function, Range of Cells.",null,null,null,"calculate a single answer such as adding a column of numbers"]
[]
[null,"Day","Calls Made",null,"Total Calls"]
[null,"Monday",60]
[null,"Tuesday",30]
[null,"Wednesday",25]
[null,"Thursday",35]
[null,"Friday",40]
[]
[]
["Ex 11","Goal: Add Top 3 Bowling Scores For Bowling Tournament.",null,null,null,"Array Calculations:"]
[null,"Type of Formula: Array Formula. Number Formula.",null,null,null,"Calculations that operate on an array of items rather than single items"]
[null,"Formula Elements: Equal sign, Built-in Function (Nested Functions),",null,null,null,"and which deliver an array of answers called a \"resultant array\"."]
[null,"Range of Cells, Array Constant with causes LARGE to deliver 3 numbers."]
[]
[null,null,"Add Top 3","Add Top 3","Add Top 3","Add Top 3"]
[]
[]
[null,"Tournament","Scores for Tom","Scores for Debbie","Scores for SheliaDawn","Scores for Chin"]
[null,"Oakland",219,243,163,156]
[null,"SF",235,268,209,285]
[null,"Fremont",207,238,174,292]
[null,"San Jose",183,236,195,276]
[null,"El Cerrito",278,194,284,230]
[null,"Berkeley",160,195,296,257]
[null,"Concord",268,173,207,249]
[null,"Vallejo",245,221,179,223]
[null,"Hayward",233,168,299,213]
[]
[]
["Ex 12","How Formulas Calculate: Order of Precedence in Excel."]
[null,1,"Parenthesis ( )",null,null,null,"Example:"]
[null,2,"Reference Operators: colon, space, comma",null,null,null,{"$f":"2>=8^(1/3)","v":true},null,{"$f":"\" \"&_xlfn.FORMULATEXT(G124)","v":" =2>=8^(1/3)"}]
[null,null,"Example of colon in range of cells: =SUM(A1:A4)"]
[null,null,"Example of intersection operator: =E12:G12 F10:F15 (retrieve what is in F12)"]
[null,null,"Example of comma (union): =SUM(E10:G10,E14:G14)"]
[null,3,"Negation (-)"]
[null,null,"Example: = -2^4 = 16"]
[null,null,"Example: = -(2^4) = -16"]
[null,null,"Example: --2+1 = 3"]
[null,4,"Converts % (1% to .01)"]
[null,5,"Exponents (^)"]
[null,null,"Example: 4^(1/2) = 2"]
[null,null,"Example: 3^2 = 9"]
[null,6,"Multiplication (*) and Division (/), left to right"]
[null,7,"Adding (+) and Subtracting (-), left to right"]
[null,8,"Ampersand (&)"]
[null,9,"Comparative symbols: =, <>, >=, <=, <, >"]
[]
["Ex 13","Steps to Find Error in formula:"]
[null,"1) Look at Formula, Look at Range Finder"]
[null,"2) Evaluate Formula"]
[null,"3) Are Formula Inputs Correct?"]
[null,"4) Is Raw Data Correct?"]
[]
[null,"F2 Key = puts formula in Edit Mode and shows the rainbow colored Range Finder"]
[null,"F9 Key = To evaluate just a single part of formula while you are in edit mode, highlight part of formula and hit the F9 key. Undo the evaluation with Ctrl + Z."]
[]
[null,"Product",null,"Product","Count"]
[null,"Carlota ",null,"Carlota",{"$f":"COUNTIFS($B$151:$B$154,D151)","v":1}]
[null,"Carlota",null,"Quad",{"$f":"COUNTIFS($B$151:$B$154,D152)","v":1}]
[null,"Quad ",null,"Tri Fly ",0]
[null,"Quad"]
[null,"Tri Fly"]
[]
[]
["Ex 14","When you MUST use ROUND Function go get correct answer:"]
[null,"1) You are required to round, like with money.",null,null,"Tax Rate"]
[null,"2) You will use the formula result in subsequent calculations.",null,null,1.4500000000000001E-2]
[]
[null,"Sales Rep","Sales","Deductions",null,"Second Argumnet of ROUND:"]
[null,"Sioux",2935.52,{"$f":"C163*$E$160","v":42.565040000000003},null,"Round to penny (hundredths position) use 2"]
[null,"Tyrone",6825.01,{"$f":"C164*$E$160","v":98.962645000000009},null,"Round to dollar (ones position) use 0"]
[null,"Chin",5404.56,78.366120000000009,null,"Round to thousands (thousands position) use -3"]
[null,"Gigi",2470.35,35.820075000000003]
[null,null,null,{"$f":"SUM(D163:D166)","v":255.71388000000005}]
[null,"Deductions"]
[null,42.57,null,"Remember: Formulas calculate on the underlying numbers, not Number Formatting"]
[null,98.96]
[null,78.37]
[null,35.82]
[null,{"$f":"SUM(B169:B172)","v":255.72}]
[]
[]
["Ex 15","3 ways to enter cell references into formulas:"]
[null,"1) Type. Be careful, though, errors may occur."]
[null,"2) Mouse: Great for when cell reference is far away."]
[null,"3) Arrow keys: great for when cell references are close."]
[null,"As long as \"Dancing Ants\" are dancing, the arrow keys will keep moving the cell reference."]
[null,"Lower left corner of Status Bar shows the \"Formula Editing Mode\""]
[null,"Ready = cell is selected."]
[null,"Enter = Arrow keys will work to put cell references in formulas."]
[null,"Point = Arrows keys are working to get cell reference and \"Dancing Ants\" are dancing."]
[null,"Edit = Arrow keys will move through formula characters. To get into this mode click I-beam cursor in formula."]
[null,"You can toggle between these modes with the F2 key."]
[]
[null,"Names","Gross Pay",{"$f":"D196","v":"Deduction 1"},{"$f":"E196","v":"Deduction 2"},"Total Deductions","Net Pay"]
[null,"Sioux Chin",3500,{"$f":"ROUND($C189*D$197,2)","v":217},{"$f":"ROUND($C189*E$197,2)","v":50.75},{"$f":"SUM(D189:E189)","v":267.75}]
[null,"Isaac Viet",3784.32,{"$f":"ROUND($C190*D$197,2)","v":234.63},{"$f":"ROUND($C190*E$197,2)","v":54.87},{"$f":"SUM(D190:E190)","v":289.5}]
[null,"Dennis Ho",8565.51,531.05999999999995,{"$f":"ROUND($C191*E$197,2)","v":124.2},{"$f":"SUM(D191:E191)","v":655.26}]
[null,"Hien Luong Pham",2627.99,162.94,{"$f":"ROUND($C192*E$197,2)","v":38.11},{"$f":"SUM(D192:E192)","v":201.05}]
[null,"Tom Carolan",9069.66,562.32000000000005,{"$f":"ROUND($C193*E$197,2)","v":131.51},{"$f":"SUM(D193:E193)","v":693.83}]
[]
[null,null,null,"Assumptions"]
[null,null,null,"Deduction 1","Deduction 2"]
[null,null,null,6.2E-2,1.4500000000000001E-2]

{"sheet":"Formula Examples (an)","index":4,"n_rows":197,"n_cols":9,"rows_kept":197,"cut":false,"hidden":false,"merged":[]}
["Ex 1","Goal: Calculate Monthly Allocation."]
[null,"Type of Formula: Number Formula."]
[null,"Formula Elements: Equal sign, cell reference,"]
[null,"math operator, number."]
[]
[null,"Annual Insurance",13500]
[null,"Monthly Allocation",{"$f":"C6/12","v":1125}]
[]
["Ex 2","Goal: Calculate deduction for each employee."]
[null,"Type of Formula: Number Formula."]
[null,"Formula Elements: Equal sign, cell reference,"]
[]
[]
[null,"Employee","Gross Pay","Deduction",null,"TaxRate"]
[null,"Sioux",2830.34,{"$f":"C15*$F$15","v":396.24760000000003},null,0.14000000000000001]
[null,"Chin",2239.9299999999998,{"$f":"C16*$F$15","v":313.59019999999998}]
[null,"Tyrone",2953.98,413.55720000000002]
[null,"Gigi",2926.74,{"$f":"C18*$F$15","v":409.74360000000001}]
[]
[null,"Goal: Calculate dynamic label for counting above the hurdle."]
[null,"Type of Formula: Text Formula."]
["Ex 3","Formula Elements: Equal sign, Text in Double Quotes, Join Symbol (&), Cell Reference"]
["Ex 4","Formula Elements: Equal sign, Text in Double Quotes, Join Symbol (&), Cell Reference, Built-in Function DOLLAR"]
[]
[null,"Goal:  Calculate number of sales above hurdle."]
[null,"Type of Formula: Number Formula."]
["Ex 5","Formula Elements: Equal sign, Built-in Function, Table Formula Nomenclature (Structured References), Cell Reference."]
["Ex 6","Formula Elements: Equal sign, Built-in Function, Table Formula Nomenclature, Comparative Operator in Double Quotes, Join Symbol (Ampersand), Cell Reference."]
[]
[null,"Product","Sales",null,"Hurdle for Counting",">=1000"]
[null,"Carlota",1144,null,{"$f":"\"Count \"&F30","v":"Count >=1000"},{"$f":"COUNTIFS(AnswerProductSales[Sales],F30)","v":4}]
[null,"Carlota",338,null,"Ex 3","Ex 4"]
[null,"Quad",1033]
[null,"Quad",832,null,"Hurdle for Counting",750,null,"**When you join Comparative"]
[null,"Tri Fly",1041,null,{"$f":"\"Count >= \"&DOLLAR(F34)","v":"Count >= $750.00"},{"$f":"COUNTIFS(AnswerProductSales[Sales],\">=\"&F34)","v":8},null,"Operator to Cell Reference, you"]
[null,"Quad",955,null,"Ex 5","Ex 6",null,"must put Comparative Operator"]
[null,"Tri Fly",587,null,null,null,null,"in Double Quotes."]
[null,"Quad",1190]
[null,"Tri Fly",945]
[null,"Quad",973]
[null,"Tri Fly",225]
[]
[]
[]
["Ex 7","Goal: Lookup Category to specify the type of sale it is: Below Par, Par, Excellent."]
[null,"Type of Formula: Lookup Formula. Text Formula."]
[null,"Formula Elements: Equal sign, Built-in Function, Cell Reference, Array Constant,"]
[null,"Function argument elements."]
[]
[null,"Sales Rep","Sales","Category",null,"Sales","Category",null,"VLOOKUP function:"]
[null,"Sioux",2935.52,{"$f":"VLOOKUP(C51,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Below Par"},null,0,"Below Par",null,"1) Looks at an item"]
[null,"Tyrone",6825.01,{"$f":"VLOOKUP(C52,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Excellent"},null,3000,"Par",null,"2) Tries to find a match in the first column of the lookup table"]
[null,"Chin",5404.56,{"$f":"VLOOKUP(C53,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Par"},null,6000,"Excellent",null,"Exact Match: tries to find exact match"]
[null,"Gigi",2470.35,{"$f":"VLOOKUP(C54,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Below Par"},null,null,null,null,"Approximate Match: bumps into first biggest item and jumps back one row"]
[null,"Sioux",5246.69,{"$f":"VLOOKUP(C55,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Par"},null,null,null,null,"3) retrieves something from a specified column"]
[null,"Tyrone",6768.17,{"$f":"VLOOKUP(C56,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Excellent"},null,null,null,null,"4) brings it back the  cell."]
[null,"Gigi",6664.08,{"$f":"VLOOKUP(C57,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Excellent"}]
[null,"Tyrone",3513.37,{"$f":"VLOOKUP(C58,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Par"},null,null,null,null,"VLOOKUP: =VLOOKUP( lookup_value , table_array , col_index_num , [range_lookup] )"]
[null,"Gigi",4879.75,{"$f":"VLOOKUP(C59,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Par"},null,null,null,null,"lookup_value = thing you look at BEFORE you go over to the table."]
[null,"Chin",2525.66,{"$f":"VLOOKUP(C60,{0,\"Below Par\";3000,\"Par\";6000,\"Excellent\"},2)","v":"Below Par"},null,null,null,null,"table_array = vertical table = VLOOKUP table"]
[null,null,null,null,null,null,null,null,"col_index_num = which column in the table has the thing you want to go and get and bring back to the cell."]
[null,null,null,null,null,null,null,null,"[range_lookup] = Exact Match = FALSE or 0. Approximate Match = TRUE or 1 or omitted."]
["Ex 8","Goal: Determine bonus amount for each employee."]
[null,"Type of Formula: Number Formula."]
[null,"Formula Elements: Equal Sign, Relative Cell Reference, Comparative Operator,"]
[null,"Absolute Cell Reference, Number typed into formula."]
[]
[null,"Sales Rep","Sales","Bonus",null,"Hurdle:",3500,null,"IF function:"]
[null,"Sioux",2935.52,{"$f":"IF(C69>=$G$68,$G$69,0)","v":0},null,"Bonus:",75,null,"Use IF function to put one of two things into a cell or formulas."]
[null,"Tyrone",6825.01,{"$f":"IF(C70>=$G$68,$G$69,0)","v":75},null,null,null,null,"logical_test argument gets the Logical Formula that comes out to be TRUE or FALSE."]
[null,"Chin",5404.56,75,null,null,null,null,"value_if_true argument is what will go in cell if Logical Formula gets a TRUE."]
[null,"Gigi",2470.35,0,null,null,null,null,"value_if_false argument is what will go in cell if Logical Formula gets a FALSE."]
[null,"SheliaDawn",3500,{"$f":"IF(C73>=$G$68,$G$69,0)","v":75}]
[]
["Ex 9","Goal: Determine If Debits = Credits"]
[null,"Type of Formula: Logical Formula."]
[null,"Formula Elements: Equal sign, Cell Reference,"]
[null,"Equal sign (as Comparative Operator), Cell Reference"]
[]
[null,"Debit (DR)","Credit (CR)",null,"In Balance?"]
[null,35.74,35.74,null,{"$f":"B87=C87","v":false}]
[null,73.61,73.61]
[null,113.08,113.08]
[null,100.49,100.5]
[null,17.7,17.7]
[null,107.38,107.38]
[null,{"$f":"SUM(B81:B86)","v":448},448.01]
[]
[]
["Ex 10","Goal: Add Calls.",null,null,null,"Aggregate Calculations:"]
[null,"Type of Formula: Aggregate Formula. Number Formula.",null,null,null,"Calculations that take two or more items and"]
[null,"Formula Elements: Equal sign, Built-in Function, Range of Cells.",null,null,null,"calculate a single answer such as adding a column of numbers"]
[]
[null,"Day","Calls Made",null,"Total Calls"]
[null,"Monday",60,null,{"$f":"SUM(C95:C99)","v":190}]
[null,"Tuesday",30]
[null,"Wednesday",25]
[null,"Thursday",35]
[null,"Friday",40]
[]
[]
["Ex 11","Goal: Add Top 3 Bowling Scores For Bowling Tournament.",null,null,null,"Array Calculations:"]
[null,"Type of Formula: Array Formula. Number Formula.",null,null,null,"Calculations that operate on an array of items rather than single items"]
[null,"Formula Elements: Equal sign, Built-in Function (Nested Functions),",null,null,null,"and which deliver an array of answers called a \"resultant array\"."]
[null,"Range of Cells, Array Constant with causes LARGE to deliver 3 numbers."]
[]
[null,null,"Add Top 3","Add Top 3","Add Top 3","Add Top 3"]
[null,null,{"$f":"SUM(LARGE(C111:C119,{1,2,3}))","v":791},{"$f":"SUM(LARGE(D111:D119,{1,2,3}))","v":749},{"$f":"SUM(LARGE(E111:E119,{1,2,3}))","v":879},{"$f":"SUM(LARGE(F111:F119,{1,2,3}))","v":853}]
[]
[null,"Tournament","Scores for Tom","Scores for Debbie","Scores for SheliaDawn","Scores for Chin"]
[null,"Oakland",219,243,163,156]
[null,"SF",235,268,209,285]
[null,"Fremont",207,238,174,292]
[null,"San Jose",183,236,195,276]
[null,"El Cerrito",278,194,284,230]
[null,"Berkeley",160,195,296,257]
[null,"Concord",268,173,207,249]
[null,"Vallejo",245,221,179,223]
[null,"Hayward",233,168,299,213]
[]
[]
["Ex 12","How Formulas Calculate: Order of Precedence in Excel."]
[null,1,"Parenthesis ( )",null,null,null,"Example:"]
[null,2,"Reference Operators: colon, space, comma",null,null,null,{"$f":"2>=8^(1/3)","v":true},null,{"$f":"\" \"&_xlfn.FORMULATEXT(G124)","v":" =2>=8^(1/3)"}]
[null,null,"Example of colon in range of cells: =SUM(A1:A4)"]
[null,null,"Example of intersection operator: =E12:G12 F10:F15 (retrieve what is in F12)"]
[null,null,"Example of comma (union): =SUM(E10:G10,E14:G14)"]
[null,3,"Negation (-)"]
[null,null,"Example: = -2^4 = 16"]
[null,null,"Example: = -(2^4) = -16"]
[null,null,"Example: --2+1 = 3"]
[null,4,"Converts % (1% to .01)"]
[null,5,"Exponents (^)"]
[null,null,"Example: 4^(1/2) = 2"]
[null,null,"Example: 3^2 = 9"]
[null,6,"Multiplication (*) and Division (/), left to right"]
[null,7,"Adding (+) and Subtracting (-), left to right"]
[null,8,"Ampersand (&)"]
[null,9,"Comparative symbols: =, <>, >=, <=, <, >"]
[]
["Ex 13","Steps to Find Error in formula:"]
[null,"1) Look at Formula, Look at Range Finder"]
[null,"2) Evaluate Formula"]
[null,"3) Are Formula Inputs Correct?"]
[null,"4) Is Raw Data Correct?"]
[]
[null,"F2 Key = puts formula in Edit Mode and shows the rainbow colored Range Finder"]
[null,"F9 Key = To evaluate just a single part of formula while you are in edit mode, highlight part of formula and hit the F9 key. Undo the evaluation with Ctrl + Z."]
[]
[null,"Product",null,"Product","Count"]
[null,"Carlota",null,"Carlota",{"$f":"COUNTIFS($B$151:$B$155,D151)","v":2}]
[null,"Carlota",null,"Quad",{"$f":"COUNTIFS($B$151:$B$155,D152)","v":2}]
[null,"Quad",null,"Tri Fly",1]
[null,"Quad"]
[null,"Tri Fly"]
[]
[]
["Ex 14","When you MUST use ROUND Function go get correct answer:"]
[null,"1) You are required to round, like with money.",null,null,"Tax Rate"]
[null,"2) You will use the formula result in subsequent calculations.",null,null,1.4500000000000001E-2]
[]
[null,"Sales Rep","Sales","Deductions",null,"Second Argumnet of ROUND:"]
[null,"Sioux",2935.52,{"$f":"ROUND(C163*$E$160,2)","v":42.57},null,"Round to penny (hundredths position) use 2"]
[null,"Tyrone",6825.01,{"$f":"ROUND(C164*$E$160,2)","v":98.96},null,"Round to dollar (ones position) use 0"]
[null,"Chin",5404.56,78.37,null,"Round to thousands (thousands position) use -3"]
[null,"Gigi",2470.35,35.82]
[null,null,null,{"$f":"SUM(D163:D166)","v":255.72}]
[null,"Deductions"]
[null,42.57,null,"Remember: Formulas calculate on the underlying numbers, not Number Formatting"]
[null,98.96]
[null,78.37]
[null,35.82]
[null,{"$f":"SUM(B169:B172)","v":255.72}]
[]
[]
["Ex 15","3 ways to enter cell references into formulas:"]
[null,"1) Type. Be careful, though, errors may occur."]
[null,"2) Mouse: Great for when cell reference is far away."]
[null,"3) Arrow keys: great for when cell references are close."]
[null,"As long as \"Dancing Ants\" are dancing, the arrow keys will keep moving the cell reference."]
[null,"Lower left corner of Status Bar shows the \"Formula Editing Mode\""]
[null,"Ready = cell is selected."]
[null,"Enter = Arrow keys will work to put cell references in formulas."]
[null,"Point = Arrows keys are working to get cell reference and \"Dancing Ants\" are dancing."]
[null,"Edit = Arrow keys will move through formula characters. To get into this mode click I-beam cursor in formula."]
[null,"You can toggle between these modes with the F2 key."]
[]
[null,"Names","Gross Pay",{"$f":"D196","v":"Deduction 1"},{"$f":"E196","v":"Deduction 2"},"Total Deductions","Net Pay"]
[null,"Sioux Chin",3500,{"$f":"ROUND($C189*D$197,2)","v":217},{"$f":"ROUND($C189*E$197,2)","v":50.75},{"$f":"SUM(D189:E189)","v":267.75},{"$f":"C189-F189","v":3232.25}]
[null,"Isaac Viet",3784.32,{"$f":"ROUND($C190*D$197,2)","v":234.63},{"$f":"ROUND($C190*E$197,2)","v":54.87},{"$f":"SUM(D190:E190)","v":289.5},{"$f":"C190-F190","v":3494.82}]
[null,"Dennis Ho",8565.51,531.05999999999995,{"$f":"ROUND($C191*E$197,2)","v":124.2},{"$f":"SUM(D191:E191)","v":655.26},7910.25]
[null,"Hien Luong Pham",2627.99,162.94,{"$f":"ROUND($C192*E$197,2)","v":38.11},{"$f":"SUM(D192:E192)","v":201.05},2426.9399999999996]
[null,"Tom Carolan",9069.66,562.32000000000005,{"$f":"ROUND($C193*E$197,2)","v":131.51},{"$f":"SUM(D193:E193)","v":693.83},8375.83]
[]
[null,null,null,"Assumptions"]
[null,null,null,"Deduction 1","Deduction 2"]
[null,null,null,6.2E-2,1.4500000000000001E-2]

{"sheet":"Errors","index":5,"n_rows":14,"n_cols":11,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,null,null,null,null,15]
["Error Message","What it means","Example",null,null,null,"Jan","Feb","Mar"]
[{"$error":"#DIV/0!"},"Divide by zero",{"$f":"100/0","v":{"$error":"#DIV/0!"}},{"$f":"100/INDEX(G3:I7,MATCH(\"P3\",F3:F7,0),MATCH(\"Mar\",G2:I2,0))","v":{"$error":"#DIV/0!"}},null,"P1",103,62,80]
[{"$error":"#REF!"},"Formula is using cell reference that has been deleted, or other invalid cell reference",{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"K1","v":15},null,"P2",118,93,75]
[{"$error":"#NAME?"},"Excel built-in function misspelled, Defined Name misspelled, or \"text\" (word data) in formula is not in double quotes.",{"$f":"AVERAGGE(G3:G7)","v":{"$error":"#NAME?"}},{"$f":"Jan &G3","v":{"$error":"#NAME?"}},null,"P3",111,102]
[{"$error":"#N/A"},"Not Available, like when VLOOKUP cam't find a match.",{"$f":"VLOOKUP(\"P7\",F3:I7,2,0)","v":{"$error":"#N/A"}},{"$f":"MATCH(\"P1 \",F3:F7,0)","v":{"$error":"#N/A"}},null,"P4",34,57,35]
[{"$error":"#VALUE!"},"Invalid operator or argumnet (like =\"Red\"*12 or =VLOOKUP(12,\"Red\",2,0), or Array Formula was entered without Ctrl + Shift + Enter",{"$f":"\"Red\"^2","v":{"$error":"#VALUE!"}},{"$f":"VLOOKUP(12,\"Red\",2,0)","v":{"$error":"#VALUE!"}},null,"P5",51,88,65]
[{"$error":"#NULL!"},"No Intersection for Space Operator Lookup",{"$f":"G5:I5 H3:H7","v":102},{"$f":"G3:G7 I3:I7","v":{"$error":"#NULL!"}}]
[{"$error":"#NUM!"},"Number is too big or small (number must be between -1*10^307 and 1*10^307), or invalid numeric values in a formula or function, or an iterative function like IRR cannot find an answer.",{"$f":"9E+99^3000","v":{"$error":"#NUM!"}},{"$f":"-9E+99^3000","v":{"$error":"#NUM!"}},null,"End Time","Begin Time",null,"Jan"]
["#######","Column width not wide enough to display data (values), or negative date or time",{"$f":"100000000","v":100000000},{"$f":"G10-F10","v":-0.125},null,"21:00:00","18:00:00",null,3]
["Circular Cell Reference","Formula contains the cell reference that the formula sits in, Like if SUM(A1:A3) was in cell A1 or the formula M147 that is in the cell M147. The formula doesn't know what to do because it is looking at itself."," =C11",{"$f":"ROWS(D$11:D11)","v":1},null,null,null,null,4]
[null,null,null,null,null,null,null,null,5]
[null,null,null,null,null,null,null,"Total",{"$f":"SUM(I10:I12)","v":12}]
[null,null,{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"A1","v":0},{"$f":"B1","v":0}]

{"sheet":"Errors (an)","index":6,"n_rows":14,"n_cols":11,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,null,null,null,null,15]
["Error Message","What it means","Example",null,null,null,"Jan","Feb","Mar"]
[{"$error":"#DIV/0!"},"Divide by zero",{"$f":"100/0","v":{"$error":"#DIV/0!"}},{"$f":"100/INDEX(G3:I7,MATCH(\"P3\",F3:F7,0),MATCH(\"Mar\",G2:I2,0))","v":{"$error":"#DIV/0!"}},null,"P1",103,62,80]
[{"$error":"#REF!"},"Formula is using cell reference that has been deleted, or other invalid cell reference",{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"K1","v":15},null,"P2",118,93,75]
[{"$error":"#NAME?"},"Excel built-in function misspelled, Defined Name misspelled, or \"text\" (word data) in formula is not in double quotes.",{"$f":"AVERAGGE(G3:G7)","v":{"$error":"#NAME?"}},{"$f":"Jan &G3","v":{"$error":"#NAME?"}},null,"P3",111,102]
[{"$error":"#N/A"},"Not Available, like when VLOOKUP cam't find a match.",{"$f":"VLOOKUP(\"P7\",F3:I7,2,0)","v":{"$error":"#N/A"}},{"$f":"MATCH(\"P1 \",F3:F7,0)","v":{"$error":"#N/A"}},null,"P4",34,57,35]
[{"$error":"#VALUE!"},"Invalid operator or argumnet (like =\"Red\"*12 or =VLOOKUP(12,\"Red\",2,0), or Array Formula was entered without Ctrl + Shift + Enter",{"$f":"\"Red\"^2","v":{"$error":"#VALUE!"}},{"$f":"VLOOKUP(12,\"Red\",2,0)","v":{"$error":"#VALUE!"}},null,"P5",51,88,65]
[{"$error":"#NULL!"},"No Intersection for Space Operator Lookup",{"$f":"G5:I5 H3:H7","v":102},{"$f":"G3:G7 I3:I7","v":{"$error":"#NULL!"}}]
[{"$error":"#NUM!"},"Number is too big or small (number must be between -1*10^307 and 1*10^307), or invalid numeric values in a formula or function, or an iterative function like IRR cannot find an answer.",{"$f":"9E+99^3000","v":{"$error":"#NUM!"}},{"$f":"-9E+99^3000","v":{"$error":"#NUM!"}},null,"End Time","Begin Time",null,"Jan"]
["#######","Column width not wide enough to display data (values), or negative date or time",{"$f":"100000000","v":100000000},{"$f":"G10-F10","v":-0.125},null,"21:00:00","18:00:00",null,3]
["Circular Cell Reference","Formula contains the cell reference that the formula sits in, Like if SUM(A1:A3) was in cell A1 or the formula M147 that is in the cell M147. The formula doesn't know what to do because it is looking at itself."," =C11",{"$f":"ROWS(D$11:D11)","v":1},null,null,null,null,4]
[null,null,null,null,null,null,null,null,5]
[null,null,null,null,null,null,null,"Total",{"$f":"SUM(I10:I12)","v":12}]
[null,null,{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"A1","v":0},{"$f":"B1","v":0}]

{"sheet":"Homework ==>>","index":7,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"HW(1)","index":8,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["1) In cell B8 create a formula that adds the expenses (do not include the Revenue). Then copy that formula through the range B8:F8.\n2) In cell B9 create a formula that calculates Net Income. Then copy that formula through the range B9:F9."]
[]
[null,"Monday","Tuesday","Wednesday","Thursday","Friday"]
["Revenue",1000,{"$f":"B4*(1+0.1)","v":1100},{"$f":"C4*(1+0.1)","v":1210},{"$f":"D4*(1+0.1)","v":1331},{"$f":"E4*(1+0.1)","v":1464.1000000000001}]
["Expense 1",291,228,140,268,217]
["Expense 2",200,124,270,140,164]
["Expense 3",235,225,103,233,257]
["Total Expenses"]
["Net Income"]

{"sheet":"HW(1an)","index":9,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["1) In cell B8 create a formula that adds the expenses (do not include the Revenue). Then copy that formula through the range B8:F8.\n2) In cell B9 create a formula that calculates Net Income. Then copy that formula through the range B9:F9."]
[]
[null,"Monday","Tuesday","Wednesday","Thursday","Friday"]
["Revenue",1000,{"$f":"B4*(1+0.1)","v":1100},{"$f":"C4*(1+0.1)","v":1210},{"$f":"D4*(1+0.1)","v":1331},{"$f":"E4*(1+0.1)","v":1464.1000000000001}]
["Expense 1",291,228,140,268,217]
["Expense 2",200,124,270,140,164]
["Expense 3",235,225,103,233,257]
["Total Expenses",{"$f":"SUM(B5:B7)","v":726},{"$f":"SUM(C5:C7)","v":577},{"$f":"SUM(D5:D7)","v":513},{"$f":"SUM(E5:E7)","v":641},{"$f":"SUM(F5:F7)","v":638}]
["Net Income",{"$f":"B4-B8","v":274},{"$f":"C4-C8","v":523},{"$f":"D4-D8","v":697},{"$f":"E4-E8","v":690},{"$f":"F4-F8","v":826.10000000000014}]

{"sheet":"HW(2)","index":10,"n_rows":9,"n_cols":5,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["1) In Cell E4 create a formula that adds the employee's calls, then copy the formula down the column."]
[]
["Employee","Day 1 Phone Calls","Day 2 Phone Calls","Day 3 Phone Calls","Total Calls"]
["Sioux",25,33,72]
["Tom",25,79,94]
["Chin",36,48,38]
["Pham",91,36,59]
["Shelia",54,67,58]
["Mo",89,95,27]

{"sheet":"HW(2an)","index":11,"n_rows":9,"n_cols":5,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["1) In Cell E4 create a formula that adds the employee's calls, then copy the formula down the column."]
[]
["Employee","Day 1 Phone Calls","Day 2 Phone Calls","Day 3 Phone Calls","Total Calls"]
["Sioux",25,33,72,{"$f":"SUM(B4:D4)","v":130}]
["Tom",25,79,94,198]
["Chin",36,48,38,122]
["Pham",91,36,59,186]
["Shelia",54,67,58,179]
["Mo",89,95,27,211]

{"sheet":"HW(3)","index":12,"n_rows":30,"n_cols":6,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["1) In cell F4, create a formula that will add the Sales for Sioux. Make sure that you verify that the formula is working.\n2) Find the problem with the formula in cell F8 and fix it."]
[]
["Date","SalesRep","Sales",null,"Names","Total Sales"]
["2010-03-18","Fred",73,null,"Sioux "]
["2010-03-19","Jo",121]
["2010-03-20","Fred",167]
["2010-03-21","Joe",167,null,"Names","Total Sales"]
["2010-03-22","Sue",75,null,"Sue ",{"$f":"SUMIFS(C4:C30,B4:B30,E7)","v":0}]
["2010-03-23","Fred",157]
["2010-03-24","Sue",61]
["2010-03-25","Fred",108]
["2010-03-26","Jo",139]
["2010-03-27","Sioux",130]
["2010-03-28","Sue",66]
["2010-03-29","Sioux",86]
["2010-03-30","Joe",122]
["2010-03-31","Sioux",50]
["2010-04-01","Sue",77]
["2010-04-02","Fred",140]
["2010-04-03","Sue",107]
["2010-04-04","Sioux",109]
["2010-04-05","Joe",121]
["2010-04-06","Fred",78]
["2010-04-07","Sioux",168]
["2010-04-08","Jo",88]
["2010-04-09","Chin",110]
["2010-04-10","Chin",132]
["2010-04-11","Jo",54]
["2010-04-12","Fred",58]
["2010-04-13","Sue",71]

{"sheet":"HW(3an)","index":13,"n_rows":30,"n_cols":6,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["1) In cell F4, create a formula that will add the Sales for Sioux. Make sure that you verify that the formula is working.\n2) Find the problem with the formula in cell F8 and fix it."]
[]
["Date","SalesRep","Sales",null,"Names","Total Sales"]
["2010-03-18","Fred",73,null,"Sioux",{"$f":"SUMIFS(C4:C30,B4:B30,E4)","v":543}]
["2010-03-19","Jo",121]
["2010-03-20","Fred",167]
["2010-03-21","Joe",167,null,"Names","Total Sales"]
["2010-03-22","Sue",75,null,"Sue",{"$f":"SUMIFS(C4:C30,B4:B30,E8)","v":457}]
["2010-03-23","Fred",157]
["2010-03-24","Sue",61]
["2010-03-25","Fred",108]
["2010-03-26","Jo",139]
["2010-03-27","Sioux",130]
["2010-03-28","Sue",66]
["2010-03-29","Sioux",86]
["2010-03-30","Joe",122]
["2010-03-31","Sioux",50]
["2010-04-01","Sue",77]
["2010-04-02","Fred",140]
["2010-04-03","Sue",107]
["2010-04-04","Sioux",109]
["2010-04-05","Joe",121]
["2010-04-06","Fred",78]
["2010-04-07","Sioux",168]
["2010-04-08","Jo",88]
["2010-04-09","Chin",110]
["2010-04-10","Chin",132]
["2010-04-11","Jo",54]
["2010-04-12","Fred",58]
["2010-04-13","Sue",71]

{"sheet":"HW(4)","index":14,"n_rows":30,"n_cols":5,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["1) Fix the problem with the formula in cell E4."]
[]
["Date","SalesRep","Sales",null,"Total Sales"]
["2010-03-18","Fred",73,null,{"$f":"SUM(C4:C29)","v":2764}]
["2010-03-19","Jo",121]
["2010-03-20","Fred",167]
["2010-03-21","Joe",167]
["2010-03-22","Sue",75]
["2010-03-23","Fred",157]
["2010-03-24","Sue",61]
["2010-03-25","Fred",108]
["2010-03-26","Jo",139]
["2010-03-27","Sioux",130]
["2010-03-28","Sue",66]
["2010-03-29","Sioux",86]
["2010-03-30","Joe",122]
["2010-03-31","Sioux",50]
["2010-04-01","Sue",77]
["2010-04-02","Fred",140]
["2010-04-03","Sue",107]
["2010-04-04","Sioux",109]
["2010-04-05","Joe",121]
["2010-04-06","Fred",78]
["2010-04-07","Sioux",168]
["2010-04-08","Jo",88]
["2010-04-09","Chin",110]
["2010-04-10","Chin",132]
["2010-04-11","Jo",54]
["2010-04-12","Fred",58]
["2010-04-13","Sue",71]

{"sheet":"HW(4an)","index":15,"n_rows":30,"n_cols":5,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["1) Fix the problem with the formula in cell E4."]
[]
["Date","SalesRep","Sales",null,"Total Sales"]
["2010-03-18","Fred",73,null,{"$f":"SUM(C4:C30)","v":2835}]
["2010-03-19","Jo",121]
["2010-03-20","Fred",167]
["2010-03-21","Joe",167]
["2010-03-22","Sue",75]
["2010-03-23","Fred",157]
["2010-03-24","Sue",61]
["2010-03-25","Fred",108]
["2010-03-26","Jo",139]
["2010-03-27","Sioux",130]
["2010-03-28","Sue",66]
["2010-03-29","Sioux",86]
["2010-03-30","Joe",122]
["2010-03-31","Sioux",50]
["2010-04-01","Sue",77]
["2010-04-02","Fred",140]
["2010-04-03","Sue",107]
["2010-04-04","Sioux",109]
["2010-04-05","Joe",121]
["2010-04-06","Fred",78]
["2010-04-07","Sioux",168]
["2010-04-08","Jo",88]
["2010-04-09","Chin",110]
["2010-04-10","Chin",132]
["2010-04-11","Jo",54]
["2010-04-12","Fred",58]
["2010-04-13","Sue",71]

{"sheet":"HW(5)","index":16,"n_rows":35,"n_cols":8,"rows_kept":35,"cut":false,"hidden":false,"merged":[[4,12,9,19],[13,12,18,19],[22,12,27,19]]}
["In the yellow cells next to each of the three solutions describe what makes each solution \"not efficient\". Be specific and use what we learn in class to describe what is not efficient about each solution. Then create an efficient solution in the last table."]
[]
[]
[]
[null,null,null,null,null,null,"Solution #1 for \"Count number of sales for each compressor\":"]
["Date","SalesRep","Sales","ProductSold"]
["2010-03-20","Fred",167,"Compressor 1",null,null,"ProductSold","Sales"]
["2010-03-26","Jo",139,"Compressor 1",null,null,"Compressor 1",{"$f":"COUNTIFS($D$7:$D$33,\"Compressor 1\")","v":6}]
["2010-04-06","Fred",78,"Compressor 1",null,null,"Compressor 2",{"$f":"COUNTIFS($D$7:$D$33,\"Compressor 2\")","v":10}]
["2010-04-08","Jo",88,"Compressor 1",null,null,"Compressor 3",{"$f":"COUNTIFS($D$7:$D$33,\"Compressor 3\")","v":11}]
["2010-04-12","Fred",58,"Compressor 1"]
["2010-04-13","Sue",71,"Compressor 1"]
["2010-03-21","Joe",167,"Compressor 2"]
["2010-03-25","Fred",108,"Compressor 2",null,null,"Solution #2 for \"Count number of sales for each compressor\":"]
["2010-03-27","Sioux",130,"Compressor 2"]
["2010-03-29","Sioux",86,"Compressor 2",null,null,"ProductSold","Sales"]
["2010-03-31","Sioux",50,"Compressor 2",null,null,"Compressor 1",{"$f":"COUNTIFS(D7:D33,G17)","v":6}]
["2010-04-01","Sue",77,"Compressor 2",null,null,"Compressor 2",{"$f":"COUNTIFS(D7:D33,G18)","v":10}]
["2010-04-03","Sue",107,"Compressor 2",null,null,"Compressor 3",{"$f":"COUNTIFS(D7:D33,G19)","v":11}]
["2010-04-04","Sioux",109,"Compressor 2"]
["2010-04-05","Joe",121,"Compressor 2"]
["2010-04-10","Chin",132,"Compressor 2"]
["2010-03-18","Fred",73,"Compressor 3",null,null,"Solution #3 for \"Count number of sales for each compressor\":"]
["2010-03-19","Jo",121,"Compressor 3"]
["2010-03-22","Sue",75,"Compressor 3",null,null,"ProductSold","Sales"]
["2010-03-23","Fred",157,"Compressor 3",null,null,"Compressor 1",{"$f":"COUNTA(D7:D12)","v":6}]
["2010-03-24","Sue",61,"Compressor 3",null,null,"Compressor 2",{"$f":"COUNTA(D13:D22)","v":10}]
["2010-03-28","Sue",66,"Compressor 3",null,null,"Compressor 3",{"$f":"COUNTA(D23:D33)","v":11}]
["2010-03-30","Joe",122,"Compressor 3"]
["2010-04-02","Fred",140,"Compressor 3",null,null,"Create Efficient Solution:"]
["2010-04-07","Sioux",168,"Compressor 3"]
["2010-04-09","Chin",110,"Compressor 3",null,null,"ProductSold","Sales"]
["2010-04-11","Jo",54,"Compressor 3",null,null,"Compressor 1"]
[null,null,null,null,null,null,"Compressor 2"]
[null,null,null,null,null,null,"Compressor 3"]

{"sheet":"HW(5an)","index":17,"n_rows":35,"n_cols":13,"rows_kept":35,"cut":false,"hidden":false,"merged":[[4,12,9,19],[13,12,18,19],[22,12,27,19]]}
["In the yellow cells next to each of the three solutions describe what makes each solution \"not efficient\". Be specific and use what we learn in class to describe what is not efficient about each solution. Then create an efficient solution in the last table."]
[]
[]
[]
[null,null,null,null,null,null,"Solution #1 for \"Count number of sales for each compressor\":",null,null,null,null,null,"The formula: =COUNTIFS($D$7:$D$33,\"Compressor 1\"), violates Excel's Golden Rule. By typing the criteria for counting into the formula we are being inefficient in two ways: 1) It takes longer to create the formulas because we must create each formula individually, rather than using a Relative Cell Reference and copying the formula down the column, and 2) By \"hard coding\" the formula input into the cell it makes it harder to change later and more prone to errors (backed up by research)."]
["Date","SalesRep","Sales","ProductSold"]
["2010-03-20","Fred",167,"Compressor 1",null,null,"ProductSold","Sales"]
["2010-03-26","Jo",139,"Compressor 1",null,null,"Compressor 1",{"$f":"COUNTIFS($D$7:$D$33,\"Compressor 1\")","v":6}]
["2010-04-06","Fred",78,"Compressor 1",null,null,"Compressor 2",{"$f":"COUNTIFS($D$7:$D$33,\"Compressor 2\")","v":10}]
["2010-04-08","Jo",88,"Compressor 1",null,null,"Compressor 3",{"$f":"COUNTIFS($D$7:$D$33,\"Compressor 3\")","v":11}]
["2010-04-12","Fred",58,"Compressor 1"]
["2010-04-13","Sue",71,"Compressor 1"]
["2010-03-21","Joe",167,"Compressor 2"]
["2010-03-25","Fred",108,"Compressor 2",null,null,"Solution #2 for \"Count number of sales for each compressor\":",null,null,null,null,null,"By not locking the range: D7:D33, the person had to create each formula individually, which wastes time in creating the formula solution. This method takes a long time to create as compared to the most efficient formula: =COUNTIFS($D$7:$D$33,G8)."]
["2010-03-27","Sioux",130,"Compressor 2"]
["2010-03-29","Sioux",86,"Compressor 2",null,null,"ProductSold","Sales"]
["2010-03-31","Sioux",50,"Compressor 2",null,null,"Compressor 1",{"$f":"COUNTIFS(D7:D33,G17)","v":6}]
["2010-04-01","Sue",77,"Compressor 2",null,null,"Compressor 2",{"$f":"COUNTIFS(D7:D33,G18)","v":10}]
["2010-04-03","Sue",107,"Compressor 2",null,null,"Compressor 3",{"$f":"COUNTIFS(D7:D33,G19)","v":11}]
["2010-04-04","Sioux",109,"Compressor 2"]
["2010-04-05","Joe",121,"Compressor 2"]
["2010-04-10","Chin",132,"Compressor 2"]
["2010-03-18","Fred",73,"Compressor 3",null,null,"Solution #3 for \"Count number of sales for each compressor\":",null,null,null,null,null,"The formulas require that the data set be sorted. The formulas will stop working if the data set is sorted in a different way or if some of the data changes. This method takes a long time to create as compared to the most efficient formula: =COUNTIFS($D$7:$D$33,G8)."]
["2010-03-19","Jo",121,"Compressor 3"]
["2010-03-22","Sue",75,"Compressor 3",null,null,"ProductSold","Sales"]
["2010-03-23","Fred",157,"Compressor 3",null,null,"Compressor 1",{"$f":"COUNTA(D7:D12)","v":6}]
["2010-03-24","Sue",61,"Compressor 3",null,null,"Compressor 2",{"$f":"COUNTA(D13:D22)","v":10}]
["2010-03-28","Sue",66,"Compressor 3",null,null,"Compressor 3",{"$f":"COUNTA(D23:D33)","v":11}]
["2010-03-30","Joe",122,"Compressor 3"]
["2010-04-02","Fred",140,"Compressor 3",null,null,"Create Efficient Solution:"]
["2010-04-07","Sioux",168,"Compressor 3"]
["2010-04-09","Chin",110,"Compressor 3",null,null,"ProductSold","Sales"]
["2010-04-11","Jo",54,"Compressor 3",null,null,"Compressor 1",{"$f":"COUNTIFS($D$7:$D$33,G33)","v":6}]
[null,null,null,null,null,null,"Compressor 2",10]
[null,null,null,null,null,null,"Compressor 3",11]

{"sheet":"HW(6)","index":18,"n_rows":8,"n_cols":6,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["1) Add a formula to cell F7 that adds the Sales for Sioux. The correct answer is 336!!!"]
[]
["Date","SalesRep","Sales",null,"SalesRep","Sales"]
["2010-07-09","Sue",170,null,"Sioux  "]
["2010-08-23","Joe",170]
["2010-04-07","Sioux ",168]
["2010-07-21","Sioux",168]
["2010-03-20","Fred",167]

{"sheet":"HW(6an)","index":19,"n_rows":8,"n_cols":6,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["1) Add a formula to cell F7 that adds the Sales for Sioux. The correct answer is 336!!!"]
[]
["Date","SalesRep","Sales",null,"SalesRep","Sales"]
["2010-07-09","Sue",170,null,"Sioux",{"$f":"SUMIFS(C4:C8,B4:B8,E4)","v":336}]
["2010-08-23","Joe",170]
["2010-04-07","Sioux",168]
["2010-07-21","Sioux",168]
["2010-03-20","Fred",167]

{"sheet":"HW(7)","index":20,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
["Create a Text formula (use the IF function) that will either put the word \"Net Income\" or \"Net Loss\" in cell A5 depending on the number value in cell B5. For example, if the number in cell B5 is greater than or equal to zero, then the cell should read \"Net Income\", if the the number in cell B5 is less than zero, then the cell should read \"Net Loss\"."]
[]
["Revenue",5000]
["Expenses",4000]
[null,{"$f":"B3-B4","v":1000}]

{"sheet":"HW(7an)","index":21,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
["Create a Text formula (use the IF function) that will either put the word \"Net Income\" or \"Net Loss\" in cell A5 depending on the number value in cell B5. For example, if the number in cell B5 is greater than or equal to zero, then the cell should read \"Net Income\", if the the number in cell B5 is less than zero, then the cell should read \"Net Loss\"."]
[]
["Revenue",5000]
["Expenses",4000]
[{"$f":"IF(B5<0,\"Net Loss\",\"Net Income\")","v":"Net Income"},{"$f":"B3-B4","v":1000}]

{"sheet":"HW(8)","index":22,"n_rows":15,"n_cols":5,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Add a Data Validation List drop-down to cell A4. Then create a VLOOKUP formula to lookup a person's name and e-mail address."]
[]
[]
["Employee ID","Employee Name","Email"]
[]
[]
[]
["Employee ID","Employee Name","Phone","Login","Email"]
["880-26-6416","Colby Guerra","(206)-571-8485","CGuerra","CGuerra@gmail.com"]
["880-19-1649","Andre Bader","(206)-687-3000","CBader","CBader@gmail.com"]
["880-32-7767","Christal Conaway","(206)-727-7960","CConaway","CConaway@gmail.com"]
["880-56-9343","Ossie Givens","(206)-716-7417","CGivens","CGivens@yahoo.com"]
["880-40-4308","Lashanda Maupin","(206)-561-9615","CMaupin","CMaupin@gmail.com"]
["880-78-4438","Letha Vela","(206)-695-4997","CVela","CVela@gmail.com"]
["880-13-7933","Curtis Mercier","(206)-690-2546","CMercier","CMercier@gmail.com"]

{"sheet":"HW(8an)","index":23,"n_rows":15,"n_cols":5,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Add a Data Validation List drop-down to cell A4. Then create a VLOOKUP formula to lookup a person's name and e-mail address."]
[]
[null,2,5]
["Employee ID","Employee Name","Email"]
["880-56-9343",{"$f":"VLOOKUP($A5,$A$9:$E$15,B3,0)","v":"Ossie Givens"},{"$f":"VLOOKUP($A5,$A$9:$E$15,C3,0)","v":"CGivens@yahoo.com"}]
[]
[]
["Employee ID","Employee Name","Phone","Login","Email"]
["880-26-6416","Colby Guerra","(206)-571-8485","CGuerra","CGuerra@gmail.com"]
["880-19-1649","Andre Bader","(206)-687-3000","CBader","CBader@gmail.com"]
["880-32-7767","Christal Conaway","(206)-727-7960","CConaway","CConaway@gmail.com"]
["880-56-9343","Ossie Givens","(206)-716-7417","CGivens","CGivens@yahoo.com"]
["880-40-4308","Lashanda Maupin","(206)-561-9615","CMaupin","CMaupin@gmail.com"]
["880-78-4438","Letha Vela","(206)-695-4997","CVela","CVela@gmail.com"]
["880-13-7933","Curtis Mercier","(206)-690-2546","CMercier","CMercier@gmail.com"]

{"sheet":"HW(9)","index":24,"n_rows":9,"n_cols":1,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Goal is to create an Employee Time Sheet that calculates the hours worked and total gross pay for the following three employees:"]
["1) Sioux Radcoolinator earns an hourly wage of $18.50 and worked from 9:15 AM to 5:30 PM with a 1 lunch break."]
["2) Chin Pham earns an hourly wage of $20.50 and worked from 10:00 AM to 5:30 PM with a 1/2 lunch break."]
["3) Battalion Smith earns an hourly wage of $17.00 and worked from 8:15 AM to 7:30 PM with a 1 lunch break."]
["Enter the data into the spreadsheet. "]
["Add appropriate Stylistic and Number Formatting. "]
["Create the formulas necessary to calculate the gross pay (totals hours * hourly wage) for each employee."]
["Add appropriate Page Setup."]
["You can create the Excel solution on this sheet or a new sheet."]

{"sheet":"HW(9an)","index":25,"n_rows":16,"n_cols":7,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["Goal is to create an Employee Time Sheet that calculates the hours worked and total gross pay for the following three employees:"]
["1) Sioux Radcoolinator earns an hourly wage of $18.50 and worked from 9:15 AM to 5:30 PM with a 1 lunch break."]
["2) Chin Pham earns an hourly wage of $20.50 and worked from 10:00 AM to 5:30 PM with a 1/2 lunch break."]
["3) Battalion Smith earns an hourly wage of $17.00 and worked from 8:15 AM to 7:30 PM with a 1 lunch break."]
["Enter the data into the spreadsheet. "]
["Add appropriate Stylistic and Number Formatting. "]
["Create the formulas necessary to calculate the gross pay (totals hours * hourly wage) for each employee."]
["Add appropriate Page Setup."]
["You can create the Excel solution on this sheet or a new sheet."]
[]
[]
["Name","Hourly Wage","Time In","Time Out","Lunch Break","Hours Worked","Total Gross Pay"]
["Sioux Radcoolinator",18.5,"09:15:00","17:30:00",1,{"$f":"(D13-C13)*24-E13","v":7.2499999999999982},{"$f":"ROUND(F13*B13,2)","v":134.13}]
["Chin Pham",20.5,"10:00:00","17:30:00",0.5,{"$f":"(D14-C14)*24-E14","v":6.9999999999999982},{"$f":"ROUND(F14*B14,2)","v":143.5}]
["Battalion Smith",17,"08:15:00","19:30:00",1,{"$f":"(D15-C15)*24-E15","v":10.25},{"$f":"ROUND(F15*B15,2)","v":174.25}]
["Totals:",null,null,null,null,{"$f":"SUM(F13:F15)","v":24.499999999999996},451.88]

{"sheet":"HW(10)","index":26,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Find all the errors in the data set and in the formula solution, fix them, and written a list of all the errors that you find."]
[]
[]
["Date","Product Sold",null,"Product","Count",null,"No.","List Errors You Found:"]
["2017-01-01","Quad",null,"Quad ",{"$f":"COUNTIFS($B$5:$B$12,C5)","v":0},null,1]
["22/20/2017"," Quad",null,"Carlota",{"$f":"COUNTIFS($B$5:$B$12,C6)","v":0},null,2]
["2017-01-13","Quad",null,"Tri  Fly",0,null,3]
["2017-01-28","Quad",null,null,null,null,4]
["2017-01-21","Carlota",null,null,null,null,5]
["2017-03-09","Qaud",null,null,null,null,6]
["2/31/2017","Tri Fly",null,null,null,null,7]
["2017-01-18","Quad"]

{"sheet":"HW(10an)","index":27,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Find all the errors in the data set and in the formula solution, fix them, and written a list of all the errors that you find."]
[]
[]
["Date","Product Sold",null,"Product","Count",null,"No.","List Errors You Found:"]
["2017-01-01","Quad",null,"Quad",{"$f":"COUNTIFS($B$5:$B$12,D5)","v":6},null,1,"C5 should be D5"]
["2017-02-20","Quad",null,"Carlota",{"$f":"COUNTIFS($B$5:$B$12,D6)","v":1},null,2,"Date 22/20/2017 is not a real date. Maybe it was: 2/20/2017"]
["2017-01-13","Quad",null,"Tri Fly",1,null,3,"Date 2/31/2017 is not a real date. Maybe it was: 2/28/2017"]
["2017-01-28","Quad",null,null,null,null,4,"\"Quad \" is D5 should be \"Quad\""]
["2017-01-21","Carlota",null,null,null,null,5,"\" Quad\" in B6 should be \"Quad\""]
["2017-03-09","Quad",null,null,null,null,6,"\"Qaud\" in B10 should be \"Quad\""]
["2017-02-28","Tri Fly",null,null,null,null,7,"\"Tri  Fly\" in D7 should be \"Tri Fly\""]
["2017-01-18","Quad"]

{"sheet":"HW(11)","index":28,"n_rows":16,"n_cols":4,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["Your goal is to add the 4 smallest (shortest times) for each round. Create an Array Formula that can do this."]
[]
[null,"Add 4 Smallest Times","Add 4 Smallest Times","Add 4 Smallest Times"]
[]
[]
["Below are the Fast Catch Scores (How Fast you can throw and catch a boomerang 5 times):"]
[]
["Thrower","Time Round 1","Time Round 2","Time Round 3"]
["Billy",35.9,35.24,18.46]
["Richard",30.66,19.66,40.97]
["BetseyLew",31.14,18.579999999999998,20.41]
["Taco",20.23,22.24,37.979999999999997]
["Tyrone",33.590000000000003,22.37,26.53]
["Shelia",38.729999999999997,18.39,41.54]
["Mo",24.92,42.19,32.18]
["Pham",36.03,30.03,34.369999999999997]

{"sheet":"HW(11an)","index":29,"n_rows":16,"n_cols":20,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["Your goal is to add the 4 smallest (shortest times) for each round. Create an Array Formula that can do this."]
[]
[null,"Add 4 Smallest Times","Add 4 Smallest Times","Add 4 Smallest Times",null,null,null,null,null,null,null,null,null,"Add 4 Smallest Times","Add 4 Smallest Times","Add 4 Smallest Times",null,"Add 4 Smallest Times","Add 4 Smallest Times","Add 4 Smallest Times"]
[null,{"$f":"SUM(SMALL(B9:B16,{1,2,3,4}))","v":106.95},{"$f":"SUM(SMALL(C9:C16,{1,2,3,4}))","v":78.86999999999999},{"$f":"SUM(SMALL(D9:D16,{1,2,3,4}))","v":97.580000000000013},null,null,null,null,null,null,null,null,null,{"$f":"SUM(_xlfn.AGGREGATE(15,4,B9:B16,{1,2,3,4}))","v":106.95},{"$f":"SUM(_xlfn.AGGREGATE(15,4,C9:C16,{1,2,3,4}))","v":78.86999999999999},{"$f":"SUM(_xlfn.AGGREGATE(15,4,D9:D16,{1,2,3,4}))","v":97.580000000000013},null,{"$f":"SUMPRODUCT(SMALL(B9:B16,ROW(INDIRECT(\"1:\"&$R$7))))","v":106.95},{"$f":"SUMPRODUCT(SMALL(C9:C16,ROW(INDIRECT(\"1:\"&$R$7))))","v":78.86999999999999},{"$f":"SUMPRODUCT(SMALL(D9:D16,ROW(INDIRECT(\"1:\"&$R$7))))","v":97.580000000000013}]
[]
["Below are the Fast Catch Scores (How Fast you can throw and catch a boomerang 5 times):",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Number to Add:"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,4]
["Thrower","Time Round 1","Time Round 2","Time Round 3"]
["Billy",35.9,35.24,18.46]
["Richard",30.66,19.66,40.97]
["BetseyLew",31.14,18.579999999999998,20.41]
["Taco",20.23,22.24,37.979999999999997]
["Tyrone",33.590000000000003,22.37,26.53]
["Shelia",38.729999999999997,18.39,41.54]
["Mo",24.92,42.19,32.18]
["Pham",36.03,30.03,34.369999999999997]