{"text_format":"1","workbook":"xlsx","n_sheets":39,"sheets":["SUMPRODUCT","SUMPRODUCT (2)","SP(2)","SP(2)an","SP(3)","SP(3)an","IF & AND & OR & IS","IF & AND & OR & IS (2)","CreditAnalysis","CreditAnalysis (2)","V(1)","V(1)an","V(2)","V(2)an","V(3)","V(3)an","V(4)","V(4)an","V(5)","V(5)an","V(6)","V(6)an","MATCH","MATCH (2)","I(1)","I(1)an","I(2)","I(2)an","I(3)","I(3)an","I(4)","I(4)an","I(5)","I(5)an","I(6)","I(6)an","I(7)","CHOOSE","CHOOSE (2)"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"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":"SUMPRODUCT","index":0,"n_rows":75,"n_cols":8,"rows_kept":75,"cut":false,"hidden":false,"merged":[]}
["Example 1: SUMPRODUCT to save space"]
["Units","Price","Sales"]
[5,22]
[2,23]
[4,25]
[6,24.5]
[null,"Total"]
["1 cell solution","Total",null,"SUMPRODUCT multiplies two arrays and then adds"]
[null,null,null,{"$f":"A3&\"*\"&B3&\"+\"&A4&\"*\"&B4&\"+\"&A5&\"*\"&B5&\"+\"&A6&\"*\"&B6&\" = \"&A3*B3&\"+\"&A4*B4&\"+\"&A5*B5&\"+\"&A6*B6&\" = \"&SUMPRODUCT(A3:A6,B3:B6)","v":"5*22+2*23+4*25+6*24.5 = 110+46+100+147 = 403"}]
[]
["Example 2: SUMPRODUCT because the nature of the calculation warrants it"]
["Economic\nState","Probability\nof State","Stock1\nReturn"]
["Weak",0.55000000000000004,-0.02]
["Normal",0.3,5.8999999999999997E-2]
["Boom",{"$f":"1-SUM(B13:B14)","v":0.14999999999999991},0.12]
[null,"Expected Return"]
["easier","Expected Return"]
[]
["Example 3: the demensions of the arrays in the SUMPRODUCT must be the same, if they are not, you can use a * symbol"]
[null,"Weight in\nPortfolio",0.25,0.75]
["Economic\nState","Probability\nof State","Stock1\nReturn","Stock1\nReturn"]
["Weak",0.55000000000000004,-0.02,0.05]
["Normal",0.3,5.8999999999999997E-2,6.5000000000000002E-2]
["Boom",{"$f":"1-SUM(B22:B23)","v":0.14999999999999991},0.12,8.5000000000000006E-2]
[null,null,"Expected Return of Portfolio",null,null,null,"Expected Return of Portfolio"]
[]
["Example 4: In earlier versions of Excel, you could use SUMPRODUCT to count or sum with more than 1 criteria. SUMPRODUCT cannot understand TRUE or FALSE and so you must convert TRUEs and FALSEs to 1s and 0s. The fastest calculating method is to use double negative like:\n--(A29:A34=E29) ==>\n--({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}) ==>\n{1;0;1;0;1;0}\nRun Formula Evaluator or use F9 key to see how formula works -- if you use F9 key, remember to undo or hit Esc key...",null,null,null,null,null,null,"Notes:"]
["SalesRep","Product","Sales",null,"SalesRep","Product",null,"* Define Array Formula: Formulas that perform operations on arrays instead of single cells."]
["SR1","P2",16,null,"SR1","P1",null,"* Boolean Logic: {1;0;1;0;1;0},{0;0;1;0;1;0},{16;10;12;19;11;13}"]
["SR2","P2",10,null,null,null,null,"* TRUEs and FALSEs often time need to be converted to 1s and 0s."]
["SR1","P1",12,null,"Count",null,null,"* TRUEs and FALSEscan be converted to 1s and 0s with any math operation: *1,  /1, ^1, +0, --"]
["SR2","P2",19,null,null,null,null,"* Double Negative (--) tends to be the most efficient method of converting TRUEs and FALSEs to 1s and 0s."]
["SR1","P1",11,null,"Sum",null,null,"* Since comparative operators are at the bottom of the \"Excel Order of Operations\" list, when using Equal Sign"]
["SR2","P2",13,null,null,null,null,"and Double Negative, you must put Equal Sign operation in parenthesizes."]
[]
[null,null,null,null,null,{"$f":"{1;0;1;0;1;0}","v":1},{"$f":"{0;0;1;0;1;0}","v":0},{"$f":"C29","v":16}]
[null,null,null,null,null,0,0,10]
[null,null,null,null,null,1,1,12]
["Compare Boolean Logic to how SUMIFS works: it's the same!!",null,null,null,null,0,0,19]
[{"$f":"SUMIFS(C29:C34,A29:A34,E29,B29:B34,F29)","v":23},null,null,null,null,1,1,11]
[null,null,null,null,null,0,0,13]
[null,null,null,null,null,null,null,"Total"]
[]
["Example 5: use SUMPRODUCTS because SUMIFS and COUNTIFS can't handle arrays"]
["Year",2009]
["Sales Rep","Julie"]
["Count"]
["Add"]
[null,null,null,null,"Extra Column Method"]
["Date","SalesRep","# Phone Calls",null,null,null,"Count"]
["2009-01-02","Mo",5]
["2009-02-01","Julie",2]
["2009-01-15","Mo",5]
["2009-02-01","Mo",3]
["2009-02-15","Julie",2]
["2008-12-30","Julie",2]
["2009-02-15","Mo",3]
["2009-01-28","Julie",3]
[]
["Example 5: use SUMPRODUCTS because SUMIFS and COUNTIFS can't handle arrays"]
["Month","Feb"]
["Year",2009]
["Sales Rep","Julie"]
["Count"]
["Add"]
[null,null,null,null,"Extra Column Method"]
["Date","SalesRep","# Phone Calls",null,null,null,"Count"]
["2009-01-02","Mo",5]
["2009-02-01","Julie",2]
["2009-01-15","Mo",5]
["2009-02-01","Mo",3]
["2009-02-15","Julie",2]
["2008-12-30","Julie",2]
["2009-02-15","Mo",3]
["2009-01-28","Julie",3]

{"sheet":"SUMPRODUCT (2)","index":1,"n_rows":75,"n_cols":9,"rows_kept":75,"cut":false,"hidden":false,"merged":[]}
["Example 1: SUMPRODUCT to save space"]
["Units","Price","Sales"]
[5,22,{"$f":"A3*B3","v":110}]
[2,23,{"$f":"A4*B4","v":46}]
[4,25,{"$f":"A5*B5","v":100}]
[6,24.5,{"$f":"A6*B6","v":147}]
[null,"Total",{"$f":"SUM(C3:C6)","v":403}]
["1 cell solution","Total",{"$f":"SUMPRODUCT(A3:A6,B3:B6)","v":403},"SUMPRODUCT multiplies two arrays and then adds"]
[null,null,null,{"$f":"A3&\"*\"&B3&\"+\"&A4&\"*\"&B4&\"+\"&A5&\"*\"&B5&\"+\"&A6&\"*\"&B6&\" = \"&A3*B3&\"+\"&A4*B4&\"+\"&A5*B5&\"+\"&A6*B6&\" = \"&SUMPRODUCT(A3:A6,B3:B6)","v":"5*22+2*23+4*25+6*24.5 = 110+46+100+147 = 403"}]
[]
["Example 2: SUMPRODUCT because the nature of the calculation warrants it"]
["Economic\nState","Probability\nof State","Stock1\nReturn"]
["Weak",0.55000000000000004,-0.02]
["Normal",0.3,5.8999999999999997E-2]
["Boom",{"$f":"1-SUM(B13:B14)","v":0.14999999999999991},0.12]
[null,"Expected Return",{"$f":"B13*C13+B14*C14+B15*C15","v":2.4699999999999986E-2}]
["easier","Expected Return",{"$f":"SUMPRODUCT(B13:B15,C13:C15)","v":2.4699999999999986E-2}]
[]
["Example 3: the demensions of the arrays in the SUMPRODUCT must be the same, if they are not, you can use a * symbol"]
[null,"Weight in\nPortfolio",0.25,0.75]
["Economic\nState","Probability\nof State","Stock1\nReturn","Stock1\nReturn"]
["Weak",0.55000000000000004,-0.02,0.05,null,{"$f":"C22*$B22*C$20","v":-2.7500000000000003E-3},2.0625000000000004E-2]
["Normal",0.3,5.8999999999999997E-2,6.5000000000000002E-2,null,4.4249999999999992E-3,1.4624999999999999E-2]
["Boom",{"$f":"1-SUM(B22:B23)","v":0.14999999999999991},0.12,8.5000000000000006E-2,null,4.4999999999999971E-3,9.5624999999999946E-3]
[null,null,"Expected Return of Portfolio",{"$f":"SUMPRODUCT(C22:D24*B22:B24*C20:D20)","v":5.0987499999999991E-2},null,null,"Expected Return of Portfolio",{"$f":"SUM(F22:G24)","v":5.0987499999999991E-2}]
[]
["Example 4: In earlier versions of Excel, you could use SUMPRODUCT to count or sum with more than 1 criteria. SUMPRODUCT cannot understand TRUE or FALSE and so you must convert TRUEs and FALSEs to 1s and 0s. The fastest calculating method is to use double negative like:\n--(A29:A34=E29) ==>\n--({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}) ==>\n{1;0;1;0;1;0}\nRun Formula Evaluator or use F9 key to see how formula works -- if you use F9 key, remember to undo or hit Esc key...",null,null,null,null,null,null,"Notes:"]
["SalesRep","Product","Sales",null,"SalesRep","Product",null,"* Define Array Formula: Formulas that perform operations on arrays instead of single cells."]
["SR1","P2",16,null,"SR1","P1",null,"* Boolean Logic: {1;0;1;0;1;0},{0;0;1;0;1;0},{16;10;12;19;11;13}"]
["SR2","P2",10,null,null,null,null,"* TRUEs and FALSEs often time need to be converted to 1s and 0s."]
["SR1","P1",12,null,"Count",null,null,"* TRUEs and FALSEscan be converted to 1s and 0s with any math operation: *1,  /1, ^1, +0, --"]
["SR2","P2",19,null,{"$f":"SUMPRODUCT(--(A29:A34=E29),--(B29:B34=F29))","v":2},null,null,"* Double Negative (--) tends to be the most efficient method of converting TRUEs and FALSEs to 1s and 0s."]
["SR1","P1",11,null,"Sum",null,null,"* Since comparative operators are at the bottom of the \"Excel Order of Operations\" list, when using Equal Sign"]
["SR2","P2",13,null,{"$f":"SUMPRODUCT(--(A29:A34=E29),--(B29:B34=F29),C29:C34)","v":23},null,null,"and Double Negative, you must put Equal Sign operation in parenthesizes."]
[]
[null,null,null,null,null,{"$f":"--(A29=E$29)","v":1},0,{"$f":"C29","v":16},{"$f":"F36*G36*H36","v":0}]
[null,null,null,null,null,0,0,10,0]
[null,null,null,null,null,1,1,12,12]
["Compare Boolean Logic to how SUMIFS works: it's the same!!",null,null,null,null,0,0,19,0]
[{"$f":"SUMIFS(C29:C34,A29:A34,E29,B29:B34,F29)","v":23},null,null,null,null,1,1,11,11]
[null,null,null,null,null,0,0,13,0]
[null,null,null,null,null,null,null,"Total",{"$f":"SUM(I36:I41)","v":23}]
[]
["Example 5: use SUMPRODUCTS because SUMIFS and COUNTIFS can't handle arrays"]
["Year",2009]
["Sales Rep","Julie"]
["Count",{"$f":"SUMPRODUCT(--(YEAR(A51:A58)=B45),--(B51:B58=B46))","v":3}]
["Add",{"$f":"SUMPRODUCT(--(YEAR(A51:A58)=B45),--(B51:B58=B46),C51:C58)","v":7}]
[null,null,null,null,"Extra Column Method"]
["Date","SalesRep","# Phone Calls",null,null,null,"Count"]
["2009-01-02","Mo",5,null,{"$f":"YEAR(A51)","v":2009},null,{"$f":"COUNTIFS(E51:E58,B45,B51:B58,B46)","v":3}]
["2009-02-01","Julie",2,null,2009]
["2009-01-15","Mo",5,null,2009]
["2009-02-01","Mo",3,null,2009]
["2009-02-15","Julie",2,null,2009]
["2008-12-30","Julie",2,null,2008]
["2009-02-15","Mo",3,null,2009]
["2009-01-28","Julie",3,null,2009]
[]
["Example 5: use SUMPRODUCTS because SUMIFS and COUNTIFS can't handle arrays"]
["Month","Feb"]
["Year",2009]
["Sales Rep","Julie"]
["Count",{"$f":"SUMPRODUCT(--(TEXT(A68:A75,\"mmmyyyy\")=B61&B62),--(B68:B75=B63))","v":2}]
["Add",{"$f":"SUMPRODUCT(--(TEXT(A68:A75,\"mmmyyyy\")=B61&B62),--(B68:B75=B63),C68:C75)","v":4}]
[null,null,null,null,"Extra Column Method"]
["Date","SalesRep","# Phone Calls",null,null,null,"Count"]
["2009-01-02","Mo",5,null,{"$f":"TEXT(A68,\"mmmyyyy\")","v":"Jan2009"},null,{"$f":"COUNTIFS(E68:E75,B61&B62,B68:B75,B63)","v":2}]
["2009-02-01","Julie",2,null,"Feb2009"]
["2009-01-15","Mo",5,null,"Jan2009"]
["2009-02-01","Mo",3,null,"Feb2009"]
["2009-02-15","Julie",2,null,"Feb2009"]
["2008-12-30","Julie",2,null,"Dec2008"]
["2009-02-15","Mo",3,null,"Feb2009"]
["2009-01-28","Julie",3,null,"Jan2009"]

{"sheet":"SP(2)","index":2,"n_rows":8,"n_cols":7,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Show a formula that caculate the amount of money by just entering the count of each bill. "]
[]
["Bills",100,50,20,10,5,1]
["Enter #",5,null,50,22,5,38]
[]
["Total"]
[]
["check"]

{"sheet":"SP(2)an","index":3,"n_rows":8,"n_cols":7,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Show a formula that caculate the amount of money by just entering the count of each bill. "]
[]
["Bills",100,50,20,10,5,1]
["Enter #",5,null,50,22,5,38]
[]
["Total",{"$f":"SUMPRODUCT(B3:G3,B4:G4)","v":1783}]
[]
["check",{"$f":"B3*B4+C3*C4+D3*D4+E3*E4+F3*F4+G3*G4","v":1783}]

{"sheet":"SP(3)","index":4,"n_rows":172,"n_cols":16,"rows_kept":172,"cut":false,"hidden":false,"merged":[]}
["For the Basics of SUMPRODUCT, see these videos:",null,null,null,null,"Excel Formula Efficiency 1: SUMPRODUCT function 12 Examples",null,null,null,null,null,null,null,"Rand formulas"]
[null,null,null,null,null,"Highline Excel Class 37: SUMPRODUCT function",null,null,null,null,null,null,null,{"$f":"INDEX({\"Product1\";\"Product2\";\"Product3\";\"Product4\";\"Product5\";\"Product6\";\"Product7\";\"Product8\";\"Product9\"},INT(RAND()*9+1))","v":"Product2"},{"$f":"INT(RAND()*20+6)","v":25},{"$f":"VLOOKUP(N2,{\"Product1\",2;\"Product2\",5;\"Product3\",3;\"Product4\",4;\"Product5\",5;\"Product6\",6;\"Product7\",5;\"Product8\",8;\"Product9\",10},2)+INT(RAND()*10)/10","v":5.4}]
["For the Basics of SUMIF, see these videos:",null,null,null,null,"Excel Magic Trick #203: SUMIF function formula 21 Examples"]
[]
["Show a formula for calculating Weighted Average Cost Form Transactionl data"]
[null,null,null,null,null,null,null,null,null,null,"Column Method"]
["Date","Product","Qrt Purchased","Price Each",null,"Product","WAC",null,"Must use Ctrl + Shift + Enter",null,"total",null,"Product","WAC"]
["2010-01-08","Product9",10,10.3,null,"Product1",null,null,null,null,null,null,"Product1"]
["2010-01-11","Product8",8,8.8000000000000007,null,"Product2",null,null,null,null,null,null,"Product2"]
["2010-01-12","Product7",9,5.7,null,"Product3",null,null,null,null,null,null,"Product3"]
["2010-01-13","Product4",13,4,null,"Product4",null,null,null,null,null,null,"Product4"]
["2010-01-14","Product8",15,8.4,null,"Product5",null,null,null,null,null,null,"Product5"]
["2010-01-15","Product9",12,10,null,"Product6",null,null,null,null,null,null,"Product6"]
["2010-01-18","Product2",22,5.9,null,"Product7",null,null,null,null,null,null,"Product7"]
["2010-01-19","Product6",23,6.3,null,"Product8",null,null,null,null,null,null,"Product8"]
["2010-01-20","Product2",24,5.5,null,"Product9",null,null,null,null,null,null,"Product9"]
["2010-01-21","Product7",11,5]
["2010-01-22","Product1",6,2]
["2010-01-25","Product2",22,5]
["2010-01-26","Product7",7,5.8]
["2010-01-27","Product5",11,5.9]
["2010-01-28","Product1",7,2.1]
["2010-01-29","Product8",20,8.3000000000000007]
["2010-02-01","Product7",14,5.0999999999999996]
["2010-02-02","Product7",22,5.3]
["2010-02-03","Product9",9,10.1]
["2010-02-04","Product1",10,2]
["2010-02-05","Product7",13,5.0999999999999996]
["2010-02-08","Product8",20,8]
["2010-02-09","Product8",10,8.1]
["2010-02-10","Product2",11,5.9]
["2010-02-11","Product8",8,8.4]
["2010-02-12","Product5",11,5.4]
["2010-02-15","Product9",7,10.7]
["2010-02-16","Product9",11,10.6]
["2010-02-17","Product8",16,8.3000000000000007]
["2010-02-18","Product6",13,6.6]
["2010-02-19","Product3",10,3.6]
["2010-02-22","Product5",17,5.8]
["2010-02-23","Product6",14,6.1]
["2010-02-24","Product1",15,2]
["2010-02-25","Product9",7,10.1]
["2010-02-26","Product4",9,4.5999999999999996]
["2010-03-01","Product3",10,3.1]
["2010-03-02","Product1",6,2.2999999999999998]
["2010-03-03","Product3",6,3.5]
["2010-03-04","Product3",8,3]
["2010-03-05","Product6",24,6.4]
["2010-03-08","Product4",23,4.0999999999999996]
["2010-03-09","Product6",25,6.7]
["2010-03-10","Product2",10,5.7]
["2010-03-11","Product1",18,2.2999999999999998]
["2010-03-12","Product4",24,4.5999999999999996]
["2010-03-15","Product7",8,5.4]
["2010-03-16","Product5",9,5]
["2010-03-17","Product9",25,10.9]
["2010-03-18","Product5",22,5.4]
["2010-03-19","Product2",24,5.3]
["2010-03-22","Product4",10,4.0999999999999996]
["2010-03-23","Product3",9,3.5]
["2010-03-24","Product9",24,10.4]
["2010-03-25","Product2",8,5.3]
["2010-03-26","Product3",18,3.4]
["2010-03-29","Product5",25,5.8]
["2010-03-30","Product5",17,5.2]
["2010-03-31","Product5",25,5.7]
["2010-04-01","Product8",8,8.6]
["2010-04-02","Product9",19,10.9]
["2010-04-05","Product3",15,3.6]
["2010-04-06","Product3",17,3.9]
["2010-04-07","Product6",15,6]
["2010-04-08","Product6",11,6.8]
["2010-04-09","Product8",16,8.8000000000000007]
["2010-04-12","Product5",20,5.8]
["2010-04-13","Product6",13,6.5]
["2010-04-14","Product1",11,2.5]
["2010-04-15","Product6",18,6.1]
["2010-04-16","Product2",7,5.0999999999999996]
["2010-04-19","Product2",8,5]
["2010-04-20","Product9",13,10.5]
["2010-04-21","Product8",22,8.1999999999999993]
["2010-04-22","Product7",7,5.0999999999999996]
["2010-04-23","Product1",7,2]
["2010-04-26","Product4",21,4.5]
["2010-04-27","Product8",25,8.8000000000000007]
["2010-04-28","Product1",20,2.8]
["2010-04-29","Product3",12,3]
["2010-04-30","Product9",13,10.6]
["2010-05-03","Product4",16,4.7]
["2010-05-04","Product9",25,10.8]
["2010-05-05","Product8",14,8.6999999999999993]
["2010-05-06","Product3",7,3.4]
["2010-05-07","Product2",7,5.8]
["2010-05-10","Product2",16,5.0999999999999996]
["2010-05-11","Product3",20,3.9]
["2010-05-12","Product5",24,5.7]
["2010-05-13","Product8",17,8.1999999999999993]
["2010-05-14","Product9",7,10.5]
["2010-05-17","Product5",19,5.4]
["2010-05-18","Product7",8,5.2]
["2010-05-19","Product3",10,3.5]
["2010-05-20","Product1",10,2]
["2010-05-21","Product8",16,8]
["2010-05-24","Product2",25,5.7]
["2010-05-25","Product2",18,5]
["2010-05-26","Product2",13,5.3]
["2010-05-27","Product9",17,10.4]
["2010-05-28","Product5",14,5.6]
["2010-05-31","Product5",24,5.6]
["2010-06-01","Product5",20,5.9]
["2010-06-02","Product3",25,3.3]
["2010-06-03","Product1",10,2.6]
["2010-06-04","Product5",19,5.6]
["2010-06-07","Product3",19,3.5]
["2010-06-08","Product7",12,5.7]
["2010-06-09","Product6",25,6.1]
["2010-06-10","Product1",13,2.1]
["2010-06-11","Product6",10,6.2]
["2010-06-14","Product1",9,2.1]
["2010-06-15","Product4",18,4.3]
["2010-06-16","Product3",24,3.9]
["2010-06-17","Product9",18,10.1]
["2010-06-18","Product1",15,2.7]
["2010-06-21","Product7",17,5.9]
["2010-06-22","Product7",25,5.5]
["2010-06-23","Product3",19,3.1]
["2010-06-24","Product2",15,5.3]
["2010-06-25","Product8",24,8.9]
["2010-06-28","Product5",10,5.8]
["2010-06-29","Product8",7,8]
["2010-06-30","Product9",17,10.1]
["2010-07-01","Product7",10,5.5]
["2010-07-02","Product5",23,5.0999999999999996]
["2010-07-05","Product2",16,5.5]
["2010-07-06","Product6",23,6.5]
["2010-07-07","Product5",25,5]
["2010-07-08","Product4",8,4.8]
["2010-07-09","Product6",8,6.9]
["2010-07-12","Product7",9,5]
["2010-07-13","Product3",23,3.7]
["2010-07-14","Product2",16,5.9]
["2010-07-15","Product9",9,10.9]
["2010-07-16","Product4",20,4.7]
["2010-07-19","Product4",6,4.7]
["2010-07-20","Product7",8,5.0999999999999996]
["2010-07-21","Product3",15,3.6]
["2010-07-22","Product9",11,10.4]
["2010-07-23","Product5",12,5.2]
["2010-07-26","Product4",6,4.9000000000000004]
["2010-07-27","Product3",9,3.5]
["2010-07-28","Product1",21,2.9]
["2010-07-29","Product4",15,4.2]
["2010-07-30","Product5",25,5.4]
["2010-08-02","Product1",13,2.4]
["2010-08-03","Product7",8,5.7]
["2010-08-04","Product6",13,6.2]
["2010-08-05","Product7",9,5.2]
["2010-08-06","Product3",23,3.9]
["2010-08-09","Product7",20,5.2]
["2010-08-10","Product6",8,6]
["2010-08-11","Product8",21,8.5]
["2010-08-12","Product9",20,10.5]
["2010-08-13","Product8",17,8.9]
["2010-08-16","Product8",22,8.1999999999999993]
["2010-08-17","Product3",7,3.6]
["2010-08-18","Product7",10,5.9]
["2010-08-19","Product4",8,4]
["2010-08-20","Product7",6,5.9]
["2010-08-23","Product5",11,5.5]
["2010-08-24","Product2",20,5.3]
["2010-08-25","Product6",10,6.6]
["2010-08-26","Product7",11,5.2]

{"sheet":"SP(3)an","index":5,"n_rows":172,"n_cols":16,"rows_kept":172,"cut":false,"hidden":false,"merged":[]}
["For the Basics of SUMPRODUCT, see these videos:",null,null,null,null,"Excel Formula Efficiency 1: SUMPRODUCT function 12 Examples",null,null,null,null,null,null,null,"Rand formulas"]
[null,null,null,null,null,"Highline Excel Class 37: SUMPRODUCT function",null,null,null,null,null,null,null,{"$f":"INDEX({\"Product1\";\"Product2\";\"Product3\";\"Product4\";\"Product5\";\"Product6\";\"Product7\";\"Product8\";\"Product9\"},INT(RAND()*9+1))","v":"Product1"},{"$f":"INT(RAND()*20+6)","v":11},{"$f":"VLOOKUP(N2,{\"Product1\",2;\"Product2\",5;\"Product3\",3;\"Product4\",4;\"Product5\",5;\"Product6\",6;\"Product7\",5;\"Product8\",8;\"Product9\",10},2)+INT(RAND()*10)/10","v":2.2000000000000002}]
["For the Basics of SUMIF, see these videos:",null,null,null,null,"Excel Magic Trick #203: SUMIF function formula 21 Examples"]
[]
["Show a formula for calculating Weighted Average Cost Form Transactionl data"]
[null,null,null,null,null,null,null,null,null,null,"Column Method"]
["Date","Product","Qrt Purchased","Price Each",null,"Product","WAC",null,"Must use Ctrl + Shift + Enter",null,"total",null,"Product","WAC"]
["2010-01-08","Product9",10,10.3,null,"Product1",{"$f":"SUMPRODUCT(--($B$8:$B$172=F8),$C$8:$C$172,$D$8:$D$172)/SUMIF($B$8:$B$172,F8,$C$8:$C$172)","v":2.3780104712041883},null,{"$f":"SUM(IF($B$8:$B$172=F8,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F8,$C$8:$C$172)","v":2.3780104712041883},null,{"$f":"C8*D8","v":103},null,"Product1",{"$f":"SUMIF($B$8:$B$172,M8,$K$8:$K$172)/SUMIF($B$8:$B$172,M8,$C$8:$C$172)","v":2.3780104712041883}]
["2010-01-11","Product8",8,8.8000000000000007,null,"Product2",5.427304964539009,null,{"$f":"SUM(IF($B$8:$B$172=F9,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F9,$C$8:$C$172)","v":5.427304964539009},null,{"$f":"C9*D9","v":70.400000000000006},null,"Product2",5.427304964539009]
["2010-01-12","Product7",9,5.7,null,"Product3",3.5450980392156861,null,{"$f":"SUM(IF($B$8:$B$172=F10,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F10,$C$8:$C$172)","v":3.5450980392156861},null,51.300000000000004,null,"Product3",3.5450980392156861]
["2010-01-13","Product4",13,4,null,"Product4",4.4223350253807103,null,{"$f":"SUM(IF($B$8:$B$172=F11,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F11,$C$8:$C$172)","v":4.4223350253807103},null,52,null,"Product4",4.4223350253807103]
["2010-01-14","Product8",15,8.4,null,"Product5",5.5180156657963462,null,{"$f":"SUM(IF($B$8:$B$172=F12,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F12,$C$8:$C$172)","v":5.5180156657963462},null,126,null,"Product5",5.5180156657963462]
["2010-01-15","Product9",12,10,null,"Product6",6.3640316205533596,null,{"$f":"SUM(IF($B$8:$B$172=F13,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F13,$C$8:$C$172)","v":6.3640316205533596},null,120,null,"Product6",6.3640316205533596]
["2010-01-18","Product2",22,5.9,null,"Product7",5.3963114754098367,null,{"$f":"SUM(IF($B$8:$B$172=F14,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F14,$C$8:$C$172)","v":5.3963114754098367},null,129.80000000000001,null,"Product7",5.3963114754098367]
["2010-01-19","Product6",23,6.3,null,"Product8",8.43921568627451,null,{"$f":"SUM(IF($B$8:$B$172=F15,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F15,$C$8:$C$172)","v":8.43921568627451},null,144.9,null,"Product8",8.43921568627451]
["2010-01-20","Product2",24,5.5,null,"Product9",{"$f":"SUMPRODUCT(--($B$8:$B$172=F16),$C$8:$C$172,$D$8:$D$172)/SUMIF($B$8:$B$172,F16,$C$8:$C$172)","v":10.495985401459855},null,{"$f":"SUM(IF($B$8:$B$172=F16,$C$8:$C$172*$D$8:$D$172))/SUMIF($B$8:$B$172,F16,$C$8:$C$172)","v":10.495985401459855},null,132,null,"Product9",10.495985401459855]
["2010-01-21","Product7",11,5,null,null,null,null,null,null,55]
["2010-01-22","Product1",6,2,null,null,null,null,null,null,12]
["2010-01-25","Product2",22,5,null,null,null,null,null,null,110]
["2010-01-26","Product7",7,5.8,null,null,null,null,null,null,40.6]
["2010-01-27","Product5",11,5.9,null,null,null,null,null,null,64.900000000000006]
["2010-01-28","Product1",7,2.1,null,null,null,null,null,null,14.700000000000001]
["2010-01-29","Product8",20,8.3000000000000007,null,null,null,null,null,null,166]
["2010-02-01","Product7",14,5.0999999999999996,null,null,null,null,null,null,71.399999999999991]
["2010-02-02","Product7",22,5.3,null,null,null,null,null,null,116.6]
["2010-02-03","Product9",9,10.1,null,null,null,null,null,null,90.899999999999991]
["2010-02-04","Product1",10,2,null,null,null,null,null,null,20]
["2010-02-05","Product7",13,5.0999999999999996,null,null,null,null,null,null,66.3]
["2010-02-08","Product8",20,8,null,null,null,null,null,null,160]
["2010-02-09","Product8",10,8.1,null,null,null,null,null,null,81]
["2010-02-10","Product2",11,5.9,null,null,null,null,null,null,64.900000000000006]
["2010-02-11","Product8",8,8.4,null,null,null,null,null,null,67.2]
["2010-02-12","Product5",11,5.4,null,null,null,null,null,null,59.400000000000006]
["2010-02-15","Product9",7,10.7,null,null,null,null,null,null,74.899999999999991]
["2010-02-16","Product9",11,10.6,null,null,null,null,null,null,116.6]
["2010-02-17","Product8",16,8.3000000000000007,null,null,null,null,null,null,132.80000000000001]
["2010-02-18","Product6",13,6.6,null,null,null,null,null,null,85.8]
["2010-02-19","Product3",10,3.6,null,null,null,null,null,null,36]
["2010-02-22","Product5",17,5.8,null,null,null,null,null,null,98.6]
["2010-02-23","Product6",14,6.1,null,null,null,null,null,null,85.399999999999991]
["2010-02-24","Product1",15,2,null,null,null,null,null,null,30]
["2010-02-25","Product9",7,10.1,null,null,null,null,null,null,70.7]
["2010-02-26","Product4",9,4.5999999999999996,null,null,null,null,null,null,41.4]
["2010-03-01","Product3",10,3.1,null,null,null,null,null,null,31]
["2010-03-02","Product1",6,2.2999999999999998,null,null,null,null,null,null,13.799999999999999]
["2010-03-03","Product3",6,3.5,null,null,null,null,null,null,21]
["2010-03-04","Product3",8,3,null,null,null,null,null,null,24]
["2010-03-05","Product6",24,6.4,null,null,null,null,null,null,153.60000000000002]
["2010-03-08","Product4",23,4.0999999999999996,null,null,null,null,null,null,94.3]
["2010-03-09","Product6",25,6.7,null,null,null,null,null,null,167.5]
["2010-03-10","Product2",10,5.7,null,null,null,null,null,null,57]
["2010-03-11","Product1",18,2.2999999999999998,null,null,null,null,null,null,41.4]
["2010-03-12","Product4",24,4.5999999999999996,null,null,null,null,null,null,110.39999999999999]
["2010-03-15","Product7",8,5.4,null,null,null,null,null,null,43.2]
["2010-03-16","Product5",9,5,null,null,null,null,null,null,45]
["2010-03-17","Product9",25,10.9,null,null,null,null,null,null,272.5]
["2010-03-18","Product5",22,5.4,null,null,null,null,null,null,118.80000000000001]
["2010-03-19","Product2",24,5.3,null,null,null,null,null,null,127.19999999999999]
["2010-03-22","Product4",10,4.0999999999999996,null,null,null,null,null,null,41]
["2010-03-23","Product3",9,3.5,null,null,null,null,null,null,31.5]
["2010-03-24","Product9",24,10.4,null,null,null,null,null,null,249.60000000000002]
["2010-03-25","Product2",8,5.3,null,null,null,null,null,null,42.4]
["2010-03-26","Product3",18,3.4,null,null,null,null,null,null,61.199999999999996]
["2010-03-29","Product5",25,5.8,null,null,null,null,null,null,145]
["2010-03-30","Product5",17,5.2,null,null,null,null,null,null,88.4]
["2010-03-31","Product5",25,5.7,null,null,null,null,null,null,142.5]
["2010-04-01","Product8",8,8.6,null,null,null,null,null,null,68.8]
["2010-04-02","Product9",19,10.9,null,null,null,null,null,null,207.1]
["2010-04-05","Product3",15,3.6,null,null,null,null,null,null,54]
["2010-04-06","Product3",17,3.9,null,null,null,null,null,null,66.3]
["2010-04-07","Product6",15,6,null,null,null,null,null,null,90]
["2010-04-08","Product6",11,6.8,null,null,null,null,null,null,74.8]
["2010-04-09","Product8",16,8.8000000000000007,null,null,null,null,null,null,{"$f":"C73*D73","v":140.80000000000001}]
["2010-04-12","Product5",20,5.8,null,null,null,null,null,null,116]
["2010-04-13","Product6",13,6.5,null,null,null,null,null,null,84.5]
["2010-04-14","Product1",11,2.5,null,null,null,null,null,null,27.5]
["2010-04-15","Product6",18,6.1,null,null,null,null,null,null,109.8]
["2010-04-16","Product2",7,5.0999999999999996,null,null,null,null,null,null,35.699999999999996]
["2010-04-19","Product2",8,5,null,null,null,null,null,null,40]
["2010-04-20","Product9",13,10.5,null,null,null,null,null,null,136.5]
["2010-04-21","Product8",22,8.1999999999999993,null,null,null,null,null,null,180.39999999999998]
["2010-04-22","Product7",7,5.0999999999999996,null,null,null,null,null,null,35.699999999999996]
["2010-04-23","Product1",7,2,null,null,null,null,null,null,14]
["2010-04-26","Product4",21,4.5,null,null,null,null,null,null,94.5]
["2010-04-27","Product8",25,8.8000000000000007,null,null,null,null,null,null,220.00000000000003]
["2010-04-28","Product1",20,2.8,null,null,null,null,null,null,56]
["2010-04-29","Product3",12,3,null,null,null,null,null,null,36]
["2010-04-30","Product9",13,10.6,null,null,null,null,null,null,137.79999999999998]
["2010-05-03","Product4",16,4.7,null,null,null,null,null,null,75.2]
["2010-05-04","Product9",25,10.8,null,null,null,null,null,null,270]
["2010-05-05","Product8",14,8.6999999999999993,null,null,null,null,null,null,121.79999999999998]
["2010-05-06","Product3",7,3.4,null,null,null,null,null,null,23.8]
["2010-05-07","Product2",7,5.8,null,null,null,null,null,null,40.6]
["2010-05-10","Product2",16,5.0999999999999996,null,null,null,null,null,null,81.599999999999994]
["2010-05-11","Product3",20,3.9,null,null,null,null,null,null,78]
["2010-05-12","Product5",24,5.7,null,null,null,null,null,null,136.80000000000001]
["2010-05-13","Product8",17,8.1999999999999993,null,null,null,null,null,null,139.39999999999998]
["2010-05-14","Product9",7,10.5,null,null,null,null,null,null,73.5]
["2010-05-17","Product5",19,5.4,null,null,null,null,null,null,102.60000000000001]
["2010-05-18","Product7",8,5.2,null,null,null,null,null,null,41.6]
["2010-05-19","Product3",10,3.5,null,null,null,null,null,null,35]
["2010-05-20","Product1",10,2,null,null,null,null,null,null,20]
["2010-05-21","Product8",16,8,null,null,null,null,null,null,128]
["2010-05-24","Product2",25,5.7,null,null,null,null,null,null,142.5]
["2010-05-25","Product2",18,5,null,null,null,null,null,null,90]
["2010-05-26","Product2",13,5.3,null,null,null,null,null,null,68.899999999999991]
["2010-05-27","Product9",17,10.4,null,null,null,null,null,null,176.8]
["2010-05-28","Product5",14,5.6,null,null,null,null,null,null,78.399999999999991]
["2010-05-31","Product5",24,5.6,null,null,null,null,null,null,134.39999999999998]
["2010-06-01","Product5",20,5.9,null,null,null,null,null,null,118]
["2010-06-02","Product3",25,3.3,null,null,null,null,null,null,82.5]
["2010-06-03","Product1",10,2.6,null,null,null,null,null,null,26]
["2010-06-04","Product5",19,5.6,null,null,null,null,null,null,106.39999999999999]
["2010-06-07","Product3",19,3.5,null,null,null,null,null,null,66.5]
["2010-06-08","Product7",12,5.7,null,null,null,null,null,null,68.400000000000006]
["2010-06-09","Product6",25,6.1,null,null,null,null,null,null,152.5]
["2010-06-10","Product1",13,2.1,null,null,null,null,null,null,27.3]
["2010-06-11","Product6",10,6.2,null,null,null,null,null,null,62]
["2010-06-14","Product1",9,2.1,null,null,null,null,null,null,18.900000000000002]
["2010-06-15","Product4",18,4.3,null,null,null,null,null,null,77.399999999999991]
["2010-06-16","Product3",24,3.9,null,null,null,null,null,null,93.6]
["2010-06-17","Product9",18,10.1,null,null,null,null,null,null,181.79999999999998]
["2010-06-18","Product1",15,2.7,null,null,null,null,null,null,40.5]
["2010-06-21","Product7",17,5.9,null,null,null,null,null,null,100.30000000000001]
["2010-06-22","Product7",25,5.5,null,null,null,null,null,null,137.5]
["2010-06-23","Product3",19,3.1,null,null,null,null,null,null,58.9]
["2010-06-24","Product2",15,5.3,null,null,null,null,null,null,79.5]
["2010-06-25","Product8",24,8.9,null,null,null,null,null,null,213.60000000000002]
["2010-06-28","Product5",10,5.8,null,null,null,null,null,null,58]
["2010-06-29","Product8",7,8,null,null,null,null,null,null,56]
["2010-06-30","Product9",17,10.1,null,null,null,null,null,null,171.7]
["2010-07-01","Product7",10,5.5,null,null,null,null,null,null,55]
["2010-07-02","Product5",23,5.0999999999999996,null,null,null,null,null,null,117.3]
["2010-07-05","Product2",16,5.5,null,null,null,null,null,null,88]
["2010-07-06","Product6",23,6.5,null,null,null,null,null,null,149.5]
["2010-07-07","Product5",25,5,null,null,null,null,null,null,125]
["2010-07-08","Product4",8,4.8,null,null,null,null,null,null,{"$f":"C137*D137","v":38.4}]
["2010-07-09","Product6",8,6.9,null,null,null,null,null,null,55.2]
["2010-07-12","Product7",9,5,null,null,null,null,null,null,45]
["2010-07-13","Product3",23,3.7,null,null,null,null,null,null,85.100000000000009]
["2010-07-14","Product2",16,5.9,null,null,null,null,null,null,94.4]
["2010-07-15","Product9",9,10.9,null,null,null,null,null,null,98.100000000000009]
["2010-07-16","Product4",20,4.7,null,null,null,null,null,null,94]
["2010-07-19","Product4",6,4.7,null,null,null,null,null,null,28.200000000000003]
["2010-07-20","Product7",8,5.0999999999999996,null,null,null,null,null,null,40.799999999999997]
["2010-07-21","Product3",15,3.6,null,null,null,null,null,null,54]
["2010-07-22","Product9",11,10.4,null,null,null,null,null,null,114.4]
["2010-07-23","Product5",12,5.2,null,null,null,null,null,null,62.400000000000006]
["2010-07-26","Product4",6,4.9000000000000004,null,null,null,null,null,null,29.400000000000002]
["2010-07-27","Product3",9,3.5,null,null,null,null,null,null,31.5]
["2010-07-28","Product1",21,2.9,null,null,null,null,null,null,60.9]
["2010-07-29","Product4",15,4.2,null,null,null,null,null,null,63]
["2010-07-30","Product5",25,5.4,null,null,null,null,null,null,135]
["2010-08-02","Product1",13,2.4,null,null,null,null,null,null,31.2]
["2010-08-03","Product7",8,5.7,null,null,null,null,null,null,45.6]
["2010-08-04","Product6",13,6.2,null,null,null,null,null,null,80.600000000000009]
["2010-08-05","Product7",9,5.2,null,null,null,null,null,null,46.800000000000004]
["2010-08-06","Product3",23,3.9,null,null,null,null,null,null,89.7]
["2010-08-09","Product7",20,5.2,null,null,null,null,null,null,104]
["2010-08-10","Product6",8,6,null,null,null,null,null,null,48]
["2010-08-11","Product8",21,8.5,null,null,null,null,null,null,178.5]
["2010-08-12","Product9",20,10.5,null,null,null,null,null,null,210]
["2010-08-13","Product8",17,8.9,null,null,null,null,null,null,151.30000000000001]
["2010-08-16","Product8",22,8.1999999999999993,null,null,null,null,null,null,180.39999999999998]
["2010-08-17","Product3",7,3.6,null,null,null,null,null,null,25.2]
["2010-08-18","Product7",10,5.9,null,null,null,null,null,null,59]
["2010-08-19","Product4",8,4,null,null,null,null,null,null,32]
["2010-08-20","Product7",6,5.9,null,null,null,null,null,null,35.400000000000006]
["2010-08-23","Product5",11,5.5,null,null,null,null,null,null,60.5]
["2010-08-24","Product2",20,5.3,null,null,null,null,null,null,106]
["2010-08-25","Product6",10,6.6,null,null,null,null,null,null,66]
["2010-08-26","Product7",11,5.2,null,null,null,null,null,null,57.2]

{"sheet":"IF & AND & OR & IS","index":6,"n_rows":74,"n_cols":6,"rows_kept":74,"cut":false,"hidden":false,"merged":[]}
[]
["Here is a list of what a cell can contain: TEXT, NUMBER, TRUE or FALSE, Blank cell, FORMULA, Blank from formula, error from formula"]
[{"$f":"\"TRUE FALSE logic formulas in Excel like: =\"&ADDRESS(ROW(B23),COLUMN(B23),4)&\"=\"&ADDRESS(ROW(C23),COLUMN(C23),4)&\". This formula asks the question is the content of \"&ADDRESS(ROW(B23),COLUMN(B23),4)&\" the same as \"&ADDRESS(ROW(C23),COLUMN(C23),4)&\"? The answer is \"&TEXT(B23=C23,\"0\")&\" because \"&B23&\" \"&IF(B23=C23,\"=\",\"<>\")&\" \"&C23&\". Case in words is not considered. For example Case = case is TRUE to Excel.\"","v":"TRUE FALSE logic formulas in Excel like: =B23=C23. This formula asks the question is the content of B23 the same as C23? The answer is FALSE because 15 <> 15.1. Case in words is not considered. For example Case = case is TRUE to Excel."}]
["Comparative Operators: =, <>, >, >=, <, <="]
["AND function (2 up to 255 logical tests that all must be TRUE for the AND function to deliver a TRUE to the cell or formula)"]
["OR function (2 up to 255 logical tests that if at least one is TRUE then the OR function delivers a TRUE to the cell or formula)"]
["NOT function converts TRUE to FALSE and FALSE to TRUE"]
[{"$f":"\"IS functions deliver a TRUE or FALSE to a cell or formula. These are some IS functions: \"&A38&\", \"&A40&\", \"&A42&\", \"&A43&\", \"&A44&\", \"&A47","v":"IS functions deliver a TRUE or FALSE to a cell or formula. These are some IS functions: ISTEXT function, ISNUMBER function, ISLOGICAL function, ISBLANK function, ISERROR function, ISNONTEXT function"}]
["IF function can put one of two things in a cell. The IF function has three parts: 1) Logical Test, 2) Value If True, 3) Value If False"]
["IFERROR function looks to see if there is an error from a formula. If there is an error it puts something in the cell, otherwise it lets the formula calculate."]
[]
[]
["Logic, TRUE FALSE formulas, IS & IF functions"]
[]
["List of what can go in a cell"]
["TEXT","rad"]
["NUMBER",4.2300000000000004]
[true,true]
[false,false]
["Error from formula",{"$f":"B17/0","v":{"$error":"#DIV/0!"}}]
[]
["Logical Question","No.","No.","Logical Formula","Comparative Operator","Comparative Operator"]
[{"$f":"\"Is \"&B23&E23&C23&\"?\"","v":"Is 15=15.1?"},15,15.1,{"$f":"B23=C23","v":false},"=","Equal"]
["Is 15<>15.1?",15,15.1,{"$f":"B24<>C24","v":true},"<>","Not Equal"]
["Is 15>15?",15,15,{"$f":"B25>C25","v":false},">","Great Than"]
["Is 15>=15?",15,15,{"$f":"B26>=C26","v":true},">=","Greater Than Or Equal To"]
["Is 15<15?",15,15,{"$f":"B27<C27","v":false},"<","Less Than"]
["Is 15<=15?",15,15,{"$f":"B28<=C28","v":true},"<=","Less Than Or Equal To"]
[]
[]
["Customer Credit Score",3]
["Customer Last Year Sales",500000]
["Company Credit Score Hurdle",3]
["Company Last Year Sales Hurdle",1000000]
["Rule 1: We have two logical tests and both must be met before we extend credit: AND function",null,"AND"]
["Rule 2: We have two logical tests and \"one or the other or both\" (also said in this way; 'at least one') can be met before we extend credit: OR function",null,"OR"]
[]
["ISTEXT function","rad",{"$f":"ISTEXT(B38)","v":true},{"$f":"ISTEXT(B38)","v":true},"Value refers to text."]
["ISTEXT function",23,{"$f":"ISTEXT(B39)","v":false},{"$f":"ISTEXT(B39)","v":false}]
["ISNUMBER function",12,{"$f":"ISNUMBER(B40)","v":true},{"$f":"ISNUMBER(B40)","v":true},"Value refers to a number."]
["ISNUMBER function","Rad",{"$f":"ISNUMBER(B41)","v":false},{"$f":"ISNUMBER(B41)","v":false}]
["ISLOGICAL function",true,{"$f":"ISLOGICAL(B42)","v":true},{"$f":"ISLOGICAL(B42)","v":true},"Value refers to a logical value."]
["ISBLANK function",null,{"$f":"ISBLANK(B43)","v":true},{"$f":"ISBLANK(B43)","v":true},"Value refers to an empty cell."]
["ISERROR function",{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"ISERROR(B44)","v":true},{"$f":"ISERROR(B44)","v":true},"Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)."]
["ISERR function",{"$error":"#N/A"},{"$f":"ISERR(B45)","v":false},{"$f":"ISERR(B45)","v":false},"Value refers to any error value except #N/A."]
["ISNA function",{"$error":"#N/A"},{"$f":"ISNA(B46)","v":true},{"$f":"ISNA(B46)","v":true},"Value refers to the #N/A (value not available) error value."]
["ISNONTEXT function","Excel",{"$f":"ISNONTEXT(B47)","v":false},{"$f":"ISNONTEXT(B47)","v":false},"Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)"]
[]
["IF function can put one of two things in a cell. The IF function has three parts:\n1) Logical Test\n2) Value If True\n3) Value If False.\nFor the 'Value If True' and 'Value If False', you can put numbers, text (must be in double quotes), formulas, or cell ranges"]
[]
["This example puts one of two words (text) into a cell"]
["Big List","Text or Number Formula 1","Text or Number Formula 2"]
["word1"]
[12]
["word3"]
["word4"]
[5896]
["word6"]
["word7"]
[]
["This example puts one of two numbers into a cell"]
["Your Sales For Month",6000]
["Do you get a bonus?"]
[{"$f":"\"You get Bonus if you sell \"&DOLLAR(B65)&\" or more\"","v":"You get Bonus if you sell $6,000.00 or more"}]
["Bonus Hurdle",6000]
["Bonus Amount",200]
["Your Bonus =",{"$f":"IF(B62>=B65,B66,0)","v":200}]
[]
["Put 1 of 2 Formulas in a cell"]
["Revenues",6]
["Expenses",6000]
[{"$f":"IF(B70>=B71,\"Net Income\",\"Net Loss\")","v":"Net Loss"}]
[]
["Conditional Formatting (Home Ribbon in 2007, Format menu in 2003) uses Logical Formulas"]

{"sheet":"IF & AND & OR & IS (2)","index":7,"n_rows":74,"n_cols":6,"rows_kept":74,"cut":false,"hidden":false,"merged":[]}
[]
["Here is a list of what a cell can contain: TEXT, NUMBER, TRUE or FALSE, Blank cell, FORMULA, Blank from formula, error from formula"]
[{"$f":"\"TRUE FALSE logic formulas in Excel like: =\"&ADDRESS(ROW(B23),COLUMN(B23),4)&\"=\"&ADDRESS(ROW(C23),COLUMN(C23),4)&\". This formula asks the question is the content of \"&ADDRESS(ROW(B23),COLUMN(B23),4)&\" the same as \"&ADDRESS(ROW(C23),COLUMN(C23),4)&\"? The answer is \"&TEXT(B23=C23,\"0\")&\" because \"&B23&\" \"&IF(B23=C23,\"=\",\"<>\")&\" \"&C23&\". Case in words is not considered. For example Case = case is TRUE to Excel.\"","v":"TRUE FALSE logic formulas in Excel like: =B23=C23. This formula asks the question is the content of B23 the same as C23? The answer is FALSE because 15 <> 15.1. Case in words is not considered. For example Case = case is TRUE to Excel."}]
["Comparative Operators: =, <>, >, >=, <, <="]
["AND function (2 up to 255 logical tests that all must be TRUE for the AND function to deliver a TRUE to the cell or formula)"]
["OR function (2 up to 255 logical tests that if at least one is TRUE then the OR function delivers a TRUE to the cell or formula)"]
["NOT function converts TRUE to FALSE and FALSE to TRUE"]
[{"$f":"\"IS functions deliver a TRUE or FALSE to a cell or formula. These are some IS functions: \"&A38&\", \"&A40&\", \"&A42&\", \"&A43&\", \"&A44&\", \"&A47","v":"IS functions deliver a TRUE or FALSE to a cell or formula. These are some IS functions: ISTEXT function, ISNUMBER function, ISLOGICAL function, ISBLANK function, ISERROR function, ISNONTEXT function"}]
["IF function can put one of two things in a cell. The IF function has three parts: 1) Logical Test, 2) Value If True, 3) Value If False"]
["IFERROR function looks to see if there is an error from a formula. If there is an error it puts something in the cell, otherwise it lets the formula calculate."]
[]
[]
["Logic, TRUE FALSE formulas, IS & IF functions"]
[]
["List of what can go in a cell"]
["TEXT","rad"]
["NUMBER",4.2300000000000004]
[true,true]
[false,false]
["Error from formula",{"$f":"B17/0","v":{"$error":"#DIV/0!"}}]
[]
["Logical Question","No.","No.","Logical Formula","Comparative Operator","Comparative Operator"]
[{"$f":"\"Is \"&B23&E23&C23&\"?\"","v":"Is 15=15.1?"},15,15.1,{"$f":"B23=C23","v":false},"=","Equal"]
["Is 15<>15.1?",15,15.1,{"$f":"B24<>C24","v":true},"<>","Not Equal"]
["Is 15>15?",15,15,{"$f":"B25>C25","v":false},">","Great Than"]
["Is 15>=15?",15,15,{"$f":"B26>=C26","v":true},">=","Greater Than Or Equal To"]
["Is 15<15?",15,15,{"$f":"B27<C27","v":false},"<","Less Than"]
["Is 15<=15?",15,15,{"$f":"B28<=C28","v":true},"<=","Less Than Or Equal To"]
[]
[]
["Customer Credit Score",3]
["Customer Last Year Sales",500000]
["Company Credit Score Hurdle",3]
["Company Last Year Sales Hurdle",1000000]
["Rule 1: We have two logical tests and both must be met before we extend credit: AND function",{"$f":"AND(B31>=B33,B32>=B34)","v":false},"AND"]
["Rule 2: We have two logical tests and \"one or the other or both\" (also said in this way; 'at least one') can be met before we extend credit: OR function",{"$f":"OR(B31>=B33,B32>=B34)","v":true},"OR"]
[]
["ISTEXT function","rad",{"$f":"ISTEXT(B38)","v":true},{"$f":"ISTEXT(B38)","v":true},"Value refers to text."]
["ISTEXT function",23,{"$f":"ISTEXT(B39)","v":false},{"$f":"ISTEXT(B39)","v":false}]
["ISNUMBER function",12,{"$f":"ISNUMBER(B40)","v":true},{"$f":"ISNUMBER(B40)","v":true},"Value refers to a number."]
["ISNUMBER function","Rad",{"$f":"ISNUMBER(B41)","v":false},{"$f":"ISNUMBER(B41)","v":false}]
["ISLOGICAL function",true,{"$f":"ISLOGICAL(B42)","v":true},{"$f":"ISLOGICAL(B42)","v":true},"Value refers to a logical value."]
["ISBLANK function",null,{"$f":"ISBLANK(B43)","v":true},{"$f":"ISBLANK(B43)","v":true},"Value refers to an empty cell."]
["ISERROR function",{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"ISERROR(B44)","v":true},{"$f":"ISERROR(B44)","v":true},"Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!)."]
["ISERR function",{"$error":"#N/A"},{"$f":"ISERR(B45)","v":false},{"$f":"ISERR(B45)","v":false},"Value refers to any error value except #N/A."]
["ISNA function",{"$error":"#N/A"},{"$f":"ISNA(B46)","v":true},{"$f":"ISNA(B46)","v":true},"Value refers to the #N/A (value not available) error value."]
["ISNONTEXT function","Excel",{"$f":"ISNONTEXT(B47)","v":false},{"$f":"ISNONTEXT(B47)","v":false},"Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)"]
[]
["IF function can put one of two things in a cell. The IF function has three parts:\n1) Logical Test\n2) Value If True\n3) Value If False.\nFor the 'Value If True' and 'Value If False', you can put numbers, text (must be in double quotes), formulas, or cell ranges"]
[]
["This example puts one of two words (text) into a cell"]
["Big List","Text or Number Formula 1","Text or Number Formula 2"]
["word1",{"$f":"IF(ISTEXT(A53),\"Text\",\"Number\")","v":"Text"},{"$f":"IF(ISNUMBER(A53),\"Number\",\"Text\")","v":"Text"}]
[12,{"$f":"IF(ISTEXT(A54),\"Text\",\"Number\")","v":"Number"},{"$f":"IF(ISNUMBER(A54),\"Number\",\"Text\")","v":"Number"}]
["word3","Text","Text"]
["word4","Text","Text"]
[5896,"Number","Number"]
["word6","Text","Text"]
["word7","Text","Text"]
[]
["This example puts one of two numbers into a cell"]
["Your Sales For Month",6000]
["Do you get a bonus?"]
[{"$f":"\"You get Bonus if you sell \"&DOLLAR(B65)&\" or more\"","v":"You get Bonus if you sell $6,000.00 or more"}]
["Bonus Hurdle",6000]
["Bonus Amount",200]
["Your Bonus =",{"$f":"IF(B62>=B65,B66,0)","v":200}]
[]
["Put 1 of 2 Formulas in a cell"]
["Revenues",6]
["Expenses",6000]
[{"$f":"IF(B70>=B71,\"Net Income\",\"Net Loss\")","v":"Net Loss"},{"$f":"IF(B70>=B71,B70-B71,B71-B70)","v":5994}]
[]
["Conditional Formatting (Home Ribbon in 2007, Format menu in 2003) uses Logical Formulas"]

{"sheet":"CreditAnalysis","index":8,"n_rows":18,"n_cols":9,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Rule 1",{"$f":"\"Account Not Suspended, No Overdue Balance and Last Year's Sales > \"&DOLLAR(F6)&\"\"","v":"Account Not Suspended, No Overdue Balance and Last Year's Sales > $50,000.00"}]
["Rule 2","Account Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met"]
[]
["Formula Inputs"]
["Customer Name","Account Suspended?","Rating1 Hurdle","Rating2 Hurdle","Asset Value","Last Year's Sales","Over Due Balance"]
[null,"Yes",2.5,4.5,200000,50000,"Yes"]
[]
["Customer Credit Analysis For Accounts Receivable"]
[{"$f":"A5","v":"Customer Name"},"Account Suspended?","Rating1 Hurdle","Rating2 Hurdle","Asset Value","Last Year's Sales","Over Due Balance",{"$f":"A1&CHAR(10)&B1","v":"Rule 1\nAccount Not Suspended, No Overdue Balance and Last Year's Sales > $50,000.00"},{"$f":"A2&CHAR(10)&B2","v":"Rule 2\nAccount Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met"}]
["Cust01",null,5,7.4,208339,90550]
["Cust02",null,5,5.9,374775,51255,"Yes"]
["Cust03",null,3.3,3.9,371040,56241]
["Cust04","Yes",1.2,2.4,331439,69920]
["Cust05","Yes",3.4,5.3,336505,58534,"Yes"]
["Cust06",null,4.5,6,336794,60423,"Yes"]
["Cust07",null,4.4000000000000004,2.4,500000,49850]
["Cust08",null,2.9,5.5,375800,62100]
["Cust09",null,2.5,8.9,254888,75000]

{"sheet":"CreditAnalysis (2)","index":9,"n_rows":18,"n_cols":9,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Rule 1",{"$f":"\"Account Not Suspended, No Overdue Balance and Last Year's Sales > \"&DOLLAR(F6)&\"\"","v":"Account Not Suspended, No Overdue Balance and Last Year's Sales > $50,000.00"}]
["Rule 2","Account Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met"]
[]
["Formula Inputs"]
["Customer Name","Account Suspended?","Rating1 Hurdle","Rating2 Hurdle","Asset Value","Last Year's Sales","Over Due Balance"]
[null,"Yes",2.5,4.5,200000,50000,"Yes"]
[]
["Customer Credit Analysis For Accounts Receivable"]
[{"$f":"A5","v":"Customer Name"},"Account Suspended?","Rating1 Hurdle","Rating2 Hurdle","Asset Value","Last Year's Sales","Over Due Balance",{"$f":"A1&CHAR(10)&B1","v":"Rule 1\nAccount Not Suspended, No Overdue Balance and Last Year's Sales > $50,000.00"},{"$f":"A2&CHAR(10)&B2","v":"Rule 2\nAccount Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met"}]
["Cust01",null,5,7.4,208339,90550,null,{"$f":"AND(B10<>B$6,G10<>G$6,F10>F$6)","v":true},{"$f":"AND(AND(B10<>B$6,C10>=C$6,D10>=D$6),OR(E10>=E$6,F10>=F$6,G10<>G$6))","v":true}]
["Cust02",null,5,5.9,374775,51255,"Yes",false,{"$f":"AND(AND(B11<>B$6,C11>=C$6,D11>=D$6),OR(E11>=E$6,F11>=F$6,G11<>G$6))","v":true}]
["Cust03",null,3.3,3.9,371040,56241,null,true,false]
["Cust04","Yes",1.2,2.4,331439,69920,null,false,false]
["Cust05","Yes",3.4,5.3,336505,58534,"Yes",false,false]
["Cust06",null,4.5,6,336794,60423,"Yes",false,true]
["Cust07",null,4.4000000000000004,2.4,500000,49850,null,false,false]
["Cust08",null,2.9,5.5,375800,62100,null,true,true]
["Cust09",null,2.5,8.9,254888,75000,null,true,true]

{"sheet":"V(1)","index":10,"n_rows":25,"n_cols":8,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Example 1: Lookup Exact Value"]
["SalesRep","Commission Rate",null,"SalesRep","Sales","Commission Rate","Commission","1 Formula Commission"]
["Sioux",0.05,null,"Sioux",5356,{"$f":"VLOOKUP(D3,$A$3:$B$5,2,0)","v":0.05},{"$f":"E3*F3","v":267.8},{"$f":"VLOOKUP(D3,$A$3:$B$5,2,0)*E3","v":267.8}]
["Chin",6.5000000000000002E-2,null,"Chin",6083,{"$f":"VLOOKUP(D4,$A$3:$B$5,2,0)","v":6.5000000000000002E-2},{"$f":"E4*F4","v":395.39500000000004},{"$f":"VLOOKUP(D4,$A$3:$B$5,2,0)*E4","v":395.39500000000004}]
["Twinette",7.5499999999999998E-2,null,"Twinette",5899,7.5499999999999998E-2,445.37450000000001,445.37450000000001]
[null,null,null,"Sioux",3316,0.05,165.8,165.8]
[null,null,null,"Chin",2014,6.5000000000000002E-2,130.91,130.91]
[null,null,null,"Twinette",3116,7.5499999999999998E-2,235.25799999999998,235.25799999999998]
[null,null,null,"Chin",2950,6.5000000000000002E-2,191.75,191.75]
[null,null,null,"Twinette",3690,7.5499999999999998E-2,278.59499999999997,278.59499999999997]
[null,null,null,"Chin",4184,6.5000000000000002E-2,271.96000000000004,271.96000000000004]
[null,null,null,"Chin",6472,6.5000000000000002E-2,420.68,420.68]
[]
["Example 2: Lookup Approximate Value"]
["Sales","Commission Rate","Category","SalesRep","Sales","Commission Rate","Commission","1 Formula Commission"]
[0,0,{"$f":"A16&\" <= Sales < \"&A17","v":"0 <= Sales < 1500"},"Sioux",5356,{"$f":"VLOOKUP(E16,$A$17:$B$19,2,1)","v":6.5000000000000002E-2},{"$f":"E16*F16","v":348.14},{"$f":"VLOOKUP(E16,$A$17:$B$19,2,1)*E16","v":348.14}]
[1500,0.05,{"$f":"A17&\" <= Sales < \"&A18","v":"1500 <= Sales < 5000"},"Chin",6083,7.5499999999999998E-2,{"$f":"E17*F17","v":459.26650000000001},459.26650000000001]
[5000,6.5000000000000002E-2,{"$f":"A18&\" <= Sales < \"&A19","v":"5000 <= Sales < 6000"},"Twinette",5899,6.5000000000000002E-2,383.435,383.435]
[6000,7.5499999999999998E-2,{"$f":"A19&\" <= Sales \"","v":"6000 <= Sales "},"Sioux",3316,0.05,165.8,165.8]
[null,null,null,"Chin",2014,0.05,100.7,100.7]
[null,null,null,"Twinette",3116,0.05,155.80000000000001,155.80000000000001]
[null,null,null,"Chin",2950,0.05,147.5,147.5]
[null,null,null,"Twinette",3690,0.05,184.5,184.5]
[null,null,null,"Chin",4184,0.05,209.20000000000002,209.20000000000002]
[null,null,null,"Chin",6472,7.5499999999999998E-2,488.63599999999997,488.63599999999997]

{"sheet":"V(1)an","index":11,"n_rows":25,"n_cols":8,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Example 1: Lookup Exact Value"]
["SalesRep","Commission Rate",null,"SalesRep","Sales","Commission Rate","Commission","1 Formula Commission"]
["Sioux",0.05,null,"Sioux",5356,{"$f":"VLOOKUP(D3,$A$3:$B$5,2,0)","v":0.05},{"$f":"E3*F3","v":267.8},{"$f":"VLOOKUP(D3,$A$3:$B$5,2,0)*E3","v":267.8}]
["Chin",6.5000000000000002E-2,null,"Chin",6083,{"$f":"VLOOKUP(D4,$A$3:$B$5,2,0)","v":6.5000000000000002E-2},{"$f":"E4*F4","v":395.39500000000004},{"$f":"VLOOKUP(D4,$A$3:$B$5,2,0)*E4","v":395.39500000000004}]
["Twinette",7.5499999999999998E-2,null,"Twinette",5899,7.5499999999999998E-2,445.37450000000001,445.37450000000001]
[null,null,null,"Sioux",3316,0.05,165.8,165.8]
[null,null,null,"Chin",2014,6.5000000000000002E-2,130.91,130.91]
[null,null,null,"Twinette",3116,7.5499999999999998E-2,235.25799999999998,235.25799999999998]
[null,null,null,"Chin",2950,6.5000000000000002E-2,191.75,191.75]
[null,null,null,"Twinette",3690,7.5499999999999998E-2,278.59499999999997,278.59499999999997]
[null,null,null,"Chin",4184,6.5000000000000002E-2,271.96000000000004,271.96000000000004]
[null,null,null,"Chin",6472,6.5000000000000002E-2,420.68,420.68]
[]
["Example 2: Lookup Approximate Value"]
["Sales","Commission Rate","Category","SalesRep","Sales","Commission Rate","Commission","1 Formula Commission"]
[0,0,{"$f":"A16&\" <= Sales < \"&A17","v":"0 <= Sales < 1500"},"Sioux",5356,{"$f":"VLOOKUP(E16,$A$17:$B$19,2,1)","v":6.5000000000000002E-2},{"$f":"E16*F16","v":348.14},{"$f":"VLOOKUP(E16,$A$17:$B$19,2,1)*E16","v":348.14}]
[1500,0.05,{"$f":"A17&\" <= Sales < \"&A18","v":"1500 <= Sales < 5000"},"Chin",6083,7.5499999999999998E-2,{"$f":"E17*F17","v":459.26650000000001},459.26650000000001]
[5000,6.5000000000000002E-2,{"$f":"A18&\" <= Sales < \"&A19","v":"5000 <= Sales < 6000"},"Twinette",5899,6.5000000000000002E-2,383.435,383.435]
[6000,7.5499999999999998E-2,{"$f":"A19&\" <= Sales \"","v":"6000 <= Sales "},"Sioux",3316,0.05,165.8,165.8]
[null,null,null,"Chin",2014,0.05,100.7,100.7]
[null,null,null,"Twinette",3116,0.05,155.80000000000001,155.80000000000001]
[null,null,null,"Chin",2950,0.05,147.5,147.5]
[null,null,null,"Twinette",3690,0.05,184.5,184.5]
[null,null,null,"Chin",4184,0.05,209.20000000000002,209.20000000000002]
[null,null,null,"Chin",6472,7.5499999999999998E-2,488.63599999999997,488.63599999999997]

{"sheet":"V(2)","index":12,"n_rows":13,"n_cols":6,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["Use COLUMNS function to tell VLOOKUP which column to lookup"]
["Using named ranges for lookup table is usually efficient (highlight range, click in Name Box, type name, hit Enter). Using Data Validation List helps the accuracy of your task (Alt + D + L is keyboard shortcut)."]
["COLUMNS function tells you how many columns there are in a range. "]
["VLOOKUP, Named Ranges, Exact Match = 0, COLUMNS & Data Validation List"]
[{"$f":"A8","v":"Name"},"Test 01","Test 02","Test 03","Test 04","Test 05"]
["Tu Pham",{"$f":"VLOOKUP($A6,GradeTable,COLUMNS($A6:B6),0)","v":58},{"$f":"VLOOKUP($A6,GradeTable,COLUMNS($A6:C6),0)","v":43},{"$f":"VLOOKUP($A6,GradeTable,COLUMNS($A6:D6),0)","v":77},{"$f":"VLOOKUP($A6,GradeTable,COLUMNS($A6:E6),0)","v":41},{"$f":"VLOOKUP($A6,GradeTable,COLUMNS($A6:F6),0)","v":32}]
[]
["Name","Test 01","Test 02","Test 03","Test 04","Test 05"]
["Siuox RadCool",94,0,7,4,53]
["Dina Kim",38,48,52,71,16]
["Dennis Ho",67,99,60,54,7]
["Tu Pham",58,43,77,41,32]
["Fred Tap",43,93,3,85,60]

{"sheet":"V(2)an","index":13,"n_rows":13,"n_cols":6,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["Use COLUMNS function to tell VLOOKUP which column to lookup"]
["Using named ranges for lookup table is usually efficient (highlight range, click in Name Box, type name, hit Enter). Using Data Validation List helps the accuracy of your task (Alt + D + L is keyboard shortcut)."]
["COLUMNS function tells you how many columns there are in a range. "]
["VLOOKUP, Named Ranges, Exact Match = 0, COLUMNS & Data Validation List"]
[{"$f":"A8","v":"Name"},"Test 01","Test 02","Test 03","Test 04","Test 05"]
["Tu Pham"]
[]
["Name","Test 01","Test 02","Test 03","Test 04","Test 05"]
["Siuox RadCool",94,0,7,4,53]
["Dina Kim",38,48,52,71,16]
["Dennis Ho",67,99,60,54,7]
["Tu Pham",58,43,77,41,32]
["Fred Tap",43,93,3,85,60]

{"sheet":"V(3)","index":14,"n_rows":13,"n_cols":7,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["VLOOKUP, Approximate = omitted, 3 VLOOKUPS in one formula"]
["Sales","Commission Paid",null,null,null,"$55,000 check 1","$55,000 check 2"]
[55000,null,null,null,null,{"$f":"D12+(A3-B12)*C12","v":1225},{"$f":"(B8-B7)*C7+(B9-B8)*C8+(B10-B9)*C9+(B11-B10)*C10+(B12-B11)*C11+(A3-B12)*C12","v":1225}]
[]
["Commission Table"]
["Category","Sales","Rate","Commission from Previous Bracket"]
[{"$f":"DOLLAR(B7)&\" <= \"&B$6&\" < \"&DOLLAR(B8)","v":"$0.00 <= Sales < $5,000.00"},0,0]
["$5,000.00 <= Sales < $10,000.00",5000,5.0000000000000001E-3,0]
["$10,000.00 <= Sales < $20,000.00",10000,0.01,{"$f":"(B9-B8)*C8+D8","v":25}]
["$20,000.00 <= Sales < $30,000.00",20000,0.02,{"$f":"(B10-B9)*C9+D9","v":125}]
["$30,000.00 <= Sales < $50,000.00",30000,0.03,{"$f":"(B11-B10)*C10+D10","v":325}]
["$50,000.00 <= Sales < $100,000.00",50000,0.06,{"$f":"(B12-B11)*C11+D11","v":925}]
[{"$f":"DOLLAR(B13)&\" <= \"&B$6","v":"$100,000.00 <= Sales"},100000,0.1,{"$f":"(B13-B12)*C12+D12","v":3925}]

{"sheet":"V(3)an","index":15,"n_rows":13,"n_cols":7,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["VLOOKUP, Approximate = omitted, 3 VLOOKUPS in one formula"]
["Sales","Commission Paid",null,null,null,"$55,000 check 1","$55,000 check 2"]
[55000,{"$f":"VLOOKUP(A3,B7:D13,3)+(A3-VLOOKUP(A3,B7:D13,1))*VLOOKUP(A3,B7:D13,2)","v":1225},null,null,null,{"$f":"D12+(A3-B12)*C12","v":1225},{"$f":"(B8-B7)*C7+(B9-B8)*C8+(B10-B9)*C9+(B11-B10)*C10+(B12-B11)*C11+(A3-B12)*C12","v":1225}]
[]
["Commission Table"]
["Category","Sales","Rate","Commission from Previous Bracket"]
[{"$f":"DOLLAR(B7)&\" <= \"&B$6&\" < \"&DOLLAR(B8)","v":"$0.00 <= Sales < $5,000.00"},0,0]
["$5,000.00 <= Sales < $10,000.00",5000,5.0000000000000001E-3,0]
["$10,000.00 <= Sales < $20,000.00",10000,0.01,{"$f":"(B9-B8)*C8+D8","v":25}]
["$20,000.00 <= Sales < $30,000.00",20000,0.02,{"$f":"(B10-B9)*C9+D9","v":125}]
["$30,000.00 <= Sales < $50,000.00",30000,0.03,{"$f":"(B11-B10)*C10+D10","v":325}]
["$50,000.00 <= Sales < $100,000.00",50000,0.06,{"$f":"(B12-B11)*C11+D11","v":925}]
[{"$f":"DOLLAR(B13)&\" <= \"&B$6","v":"$100,000.00 <= Sales"},100000,0.1,{"$f":"(B13-B12)*C12+D12","v":3925}]

{"sheet":"V(4)","index":16,"n_rows":185,"n_cols":8,"rows_kept":185,"cut":false,"hidden":false,"merged":[]}
[{"$f":"ROW()&\")\"","v":"1)"},"VLOOKUP (Exact match = 0) function and the Ampersand, &, can be used together to create a new text item"]
[{"$f":"ROW()&\")\"","v":"2)"},"Hiding a column temporarily will allow you to use the \"Double-click the fill handle\" trick"]
[]
[null,"Data Dump",null,null,null,null,"Our System"]
[null,"Type","Sales","Customer","Our Type",null,"Type","Number"]
[null,"Kite",1221,"Yuliya",null,null,"Kite",5560]
[null,"Boom",1101,"Yuliya",null,null,"Boom",5590]
[null,"Kite",1451,"Yuliya",null,null,"Toy",5510]
[null,"Toy",1293,"Megan",null,null,"Other",5540]
[null,"Kite",1950,"Rasik"]
[null,"Toy",1956,"Joel"]
[null,"Other",1163,"Rasik"]
[null,"Boom",1177,"Rasik"]
[null,"Other",1105,"Igor"]
[null,"Kite",1797,"Megan"]
[null,"Boom",1692,"Debbie"]
[null,"Kite",1874,"Wendy"]
[null,"Boom",1565,"Olga"]
[null,"Boom",1274,"Igor"]
[null,"Boom",1133,"Igor"]
[null,"Other",1687,"Olga"]
[null,"Boom",1083,"Igor"]
[null,"Kite",1094,"Suix"]
[null,"Boom",1146,"Joel"]
[null,"Toy",1776,"Al"]
[null,"Other",1985,"Al"]
[null,"Other",1334,"Debbie"]
[null,"Toy",1602,"Yuliya"]
[null,"Kite",1163,"Al"]
[null,"Toy",1715,"Joel"]
[null,"Boom",1155,"Rasik"]
[null,"Toy",1330,"Igor"]
[null,"Other",1972,"Debbie"]
[null,"Toy",1728,"Suix"]
[null,"Other",1698,"Yuliya"]
[null,"Kite",1759,"Wendy"]
[null,"Toy",1238,"Joel"]
[null,"Kite",1440,"Megan"]
[null,"Boom",1990,"Rasik"]
[null,"Toy",1119,"Yuliya"]
[null,"Other",1109,"Suix"]
[null,"Boom",1808,"Rasik"]
[null,"Kite",1240,"Megan"]
[null,"Kite",1964,"Al"]
[null,"Other",1872,"Suix"]
[null,"Boom",1372,"Suix"]
[null,"Other",1390,"Al"]
[null,"Boom",1158,"Rasik"]
[null,"Boom",1934,"Yuliya"]
[null,"Boom",1666,"Joel"]
[null,"Other",1647,"Megan"]
[null,"Other",1085,"Yuliya"]
[null,"Other",1359,"Al"]
[null,"Toy",1293,"Wendy"]
[null,"Boom",1249,"Megan"]
[null,"Kite",1152,"Yuliya"]
[null,"Kite",1024,"Debbie"]
[null,"Other",1525,"Suix"]
[null,"Other",1018,"Rasik"]
[null,"Kite",1229,"Al"]
[null,"Other",1371,"Megan"]
[null,"Toy",1208,"Wendy"]
[null,"Other",1785,"Debbie"]
[null,"Boom",1832,"Olga"]
[null,"Other",1740,"Olga"]
[null,"Boom",1142,"Olga"]
[null,"Kite",1499,"Rasik"]
[null,"Boom",1778,"Debbie"]
[null,"Other",1681,"Igor"]
[null,"Boom",1624,"Suix"]
[null,"Kite",1314,"Joel"]
[null,"Other",1193,"Joel"]
[null,"Other",1235,"Al"]
[null,"Other",1227,"Megan"]
[null,"Toy",1567,"Debbie"]
[null,"Other",1694,"Olga"]
[null,"Kite",1664,"Al"]
[null,"Toy",1893,"Debbie"]
[null,"Other",1128,"Al"]
[null,"Toy",1408,"Olga"]
[null,"Kite",1040,"Rasik"]
[null,"Toy",1992,"Megan"]
[null,"Other",1870,"Joel"]
[null,"Toy",1376,"Igor"]
[null,"Boom",1559,"Rasik"]
[null,"Toy",1532,"Rasik"]
[null,"Other",1876,"Olga"]
[null,"Toy",1033,"Rasik"]
[null,"Boom",1324,"Wendy"]
[null,"Other",1709,"Rasik"]
[null,"Kite",1795,"Suix"]
[null,"Boom",1657,"Megan"]
[null,"Other",1434,"Olga"]
[null,"Boom",1824,"Debbie"]
[null,"Toy",1043,"Yuliya"]
[null,"Toy",1756,"Yuliya"]
[null,"Kite",1023,"Igor"]
[null,"Toy",1376,"Suix"]
[null,"Other",1561,"Al"]
[null,"Boom",1665,"Rasik"]
[null,"Kite",1452,"Igor"]
[null,"Toy",1305,"Igor"]
[null,"Toy",1965,"Igor"]
[null,"Toy",1919,"Igor"]
[null,"Toy",1826,"Igor"]
[null,"Other",1221,"Megan"]
[null,"Boom",1193,"Rasik"]
[null,"Other",1639,"Suix"]
[null,"Toy",1817,"Igor"]
[null,"Other",1288,"Yuliya"]
[null,"Kite",1350,"Igor"]
[null,"Boom",1609,"Rasik"]
[null,"Other",1270,"Yuliya"]
[null,"Kite",1143,"Suix"]
[null,"Boom",1931,"Rasik"]
[null,"Toy",1707,"Debbie"]
[null,"Boom",1983,"Debbie"]
[null,"Boom",1910,"Debbie"]
[null,"Kite",1303,"Joel"]
[null,"Other",1913,"Megan"]
[null,"Boom",1972,"Rasik"]
[null,"Boom",1253,"Al"]
[null,"Kite",1034,"Olga"]
[null,"Boom",1517,"Yuliya"]
[null,"Kite",1071,"Rasik"]
[null,"Kite",1982,"Joel"]
[null,"Boom",1189,"Wendy"]
[null,"Toy",1009,"Rasik"]
[null,"Toy",1245,"Igor"]
[null,"Other",1973,"Megan"]
[null,"Toy",1442,"Debbie"]
[null,"Toy",1873,"Debbie"]
[null,"Kite",1464,"Igor"]
[null,"Other",1159,"Al"]
[null,"Boom",1529,"Debbie"]
[null,"Toy",1809,"Joel"]
[null,"Other",1832,"Joel"]
[null,"Boom",1999,"Suix"]
[null,"Other",1115,"Yuliya"]
[null,"Toy",1518,"Megan"]
[null,"Boom",1861,"Al"]
[null,"Toy",1289,"Yuliya"]
[null,"Boom",1390,"Igor"]
[null,"Boom",1375,"Megan"]
[null,"Other",1437,"Megan"]
[null,"Boom",1333,"Megan"]
[null,"Other",1686,"Olga"]
[null,"Toy",1938,"Igor"]
[null,"Toy",1685,"Olga"]
[null,"Kite",1947,"Suix"]
[null,"Kite",1248,"Rasik"]
[null,"Other",1573,"Olga"]
[null,"Other",1811,"Olga"]
[null,"Boom",1759,"Rasik"]
[null,"Kite",1727,"Al"]
[null,"Toy",1276,"Al"]
[null,"Other",1808,"Olga"]
[null,"Other",1644,"Yuliya"]
[null,"Toy",1872,"Al"]
[null,"Kite",1461,"Olga"]
[null,"Boom",1564,"Wendy"]
[null,"Kite",1589,"Yuliya"]
[null,"Other",1989,"Olga"]
[null,"Toy",1194,"Igor"]
[null,"Toy",1116,"Rasik"]
[null,"Toy",1690,"Debbie"]
[null,"Kite",1493,"Yuliya"]
[null,"Boom",1680,"Megan"]
[null,"Kite",1916,"Olga"]
[null,"Kite",1973,"Olga"]
[null,"Boom",1378,"Rasik"]
[null,"Other",1182,"Olga"]
[null,"Toy",1252,"Yuliya"]
[null,"Toy",1101,"Al"]
[null,"Boom",1394,"Joel"]
[null,"Other",1926,"Debbie"]
[null,"Toy",1379,"Debbie"]
[null,"Toy",1528,"Igor"]
[null,"Toy",1931,"Megan"]
[null,"Boom",1780,"Igor"]
[null,"Kite",1374,"Rasik"]
[null,"Boom",1469,"Joel"]
[null,"Kite",1066,"Yuliya"]
[null,"Toy",1709,"Joel"]
[null,"Other",1901,"Debbie"]

{"sheet":"V(4)an","index":17,"n_rows":185,"n_cols":8,"rows_kept":185,"cut":false,"hidden":false,"merged":[]}
[{"$f":"ROW()&\")\"","v":"1)"},"VLOOKUP (Exact match = 0) function and the Ampersand, &, can be used together to create a new text item"]
[{"$f":"ROW()&\")\"","v":"2)"},"Hiding a column temporarily will allow you to use the \"Double-click the fill handle\" trick"]
[]
[null,"Data Dump",null,null,null,null,"Our System"]
[null,"Type","Sales","Customer","Our Type",null,"Type","Number"]
[null,"Kite",1221,"Yuliya",{"$f":"B6&\"-\"&VLOOKUP(B6,G$6:H$9,2,0)","v":"Kite-5560"},null,"Kite",5560]
[null,"Boom",1101,"Yuliya",{"$f":"B7&\"-\"&VLOOKUP(B7,G$6:H$9,2,0)","v":"Boom-5590"},null,"Boom",5590]
[null,"Kite",1451,"Yuliya","Kite-5560",null,"Toy",5510]
[null,"Toy",1293,"Megan","Toy-5510",null,"Other",5540]
[null,"Kite",1950,"Rasik","Kite-5560"]
[null,"Toy",1956,"Joel","Toy-5510"]
[null,"Other",1163,"Rasik","Other-5540"]
[null,"Boom",1177,"Rasik","Boom-5590"]
[null,"Other",1105,"Igor","Other-5540"]
[null,"Kite",1797,"Megan","Kite-5560"]
[null,"Boom",1692,"Debbie","Boom-5590"]
[null,"Kite",1874,"Wendy","Kite-5560"]
[null,"Boom",1565,"Olga","Boom-5590"]
[null,"Boom",1274,"Igor","Boom-5590"]
[null,"Boom",1133,"Igor","Boom-5590"]
[null,"Other",1687,"Olga","Other-5540"]
[null,"Boom",1083,"Igor","Boom-5590"]
[null,"Kite",1094,"Suix","Kite-5560"]
[null,"Boom",1146,"Joel","Boom-5590"]
[null,"Toy",1776,"Al","Toy-5510"]
[null,"Other",1985,"Al","Other-5540"]
[null,"Other",1334,"Debbie","Other-5540"]
[null,"Toy",1602,"Yuliya","Toy-5510"]
[null,"Kite",1163,"Al","Kite-5560"]
[null,"Toy",1715,"Joel","Toy-5510"]
[null,"Boom",1155,"Rasik","Boom-5590"]
[null,"Toy",1330,"Igor","Toy-5510"]
[null,"Other",1972,"Debbie","Other-5540"]
[null,"Toy",1728,"Suix","Toy-5510"]
[null,"Other",1698,"Yuliya","Other-5540"]
[null,"Kite",1759,"Wendy","Kite-5560"]
[null,"Toy",1238,"Joel","Toy-5510"]
[null,"Kite",1440,"Megan","Kite-5560"]
[null,"Boom",1990,"Rasik","Boom-5590"]
[null,"Toy",1119,"Yuliya","Toy-5510"]
[null,"Other",1109,"Suix","Other-5540"]
[null,"Boom",1808,"Rasik","Boom-5590"]
[null,"Kite",1240,"Megan","Kite-5560"]
[null,"Kite",1964,"Al","Kite-5560"]
[null,"Other",1872,"Suix","Other-5540"]
[null,"Boom",1372,"Suix","Boom-5590"]
[null,"Other",1390,"Al","Other-5540"]
[null,"Boom",1158,"Rasik","Boom-5590"]
[null,"Boom",1934,"Yuliya","Boom-5590"]
[null,"Boom",1666,"Joel","Boom-5590"]
[null,"Other",1647,"Megan","Other-5540"]
[null,"Other",1085,"Yuliya","Other-5540"]
[null,"Other",1359,"Al","Other-5540"]
[null,"Toy",1293,"Wendy","Toy-5510"]
[null,"Boom",1249,"Megan","Boom-5590"]
[null,"Kite",1152,"Yuliya","Kite-5560"]
[null,"Kite",1024,"Debbie","Kite-5560"]
[null,"Other",1525,"Suix","Other-5540"]
[null,"Other",1018,"Rasik","Other-5540"]
[null,"Kite",1229,"Al","Kite-5560"]
[null,"Other",1371,"Megan","Other-5540"]
[null,"Toy",1208,"Wendy","Toy-5510"]
[null,"Other",1785,"Debbie","Other-5540"]
[null,"Boom",1832,"Olga","Boom-5590"]
[null,"Other",1740,"Olga","Other-5540"]
[null,"Boom",1142,"Olga","Boom-5590"]
[null,"Kite",1499,"Rasik","Kite-5560"]
[null,"Boom",1778,"Debbie","Boom-5590"]
[null,"Other",1681,"Igor","Other-5540"]
[null,"Boom",1624,"Suix","Boom-5590"]
[null,"Kite",1314,"Joel",{"$f":"B71&\"-\"&VLOOKUP(B71,G$6:H$9,2,0)","v":"Kite-5560"}]
[null,"Other",1193,"Joel","Other-5540"]
[null,"Other",1235,"Al","Other-5540"]
[null,"Other",1227,"Megan","Other-5540"]
[null,"Toy",1567,"Debbie","Toy-5510"]
[null,"Other",1694,"Olga","Other-5540"]
[null,"Kite",1664,"Al","Kite-5560"]
[null,"Toy",1893,"Debbie","Toy-5510"]
[null,"Other",1128,"Al","Other-5540"]
[null,"Toy",1408,"Olga","Toy-5510"]
[null,"Kite",1040,"Rasik","Kite-5560"]
[null,"Toy",1992,"Megan","Toy-5510"]
[null,"Other",1870,"Joel","Other-5540"]
[null,"Toy",1376,"Igor","Toy-5510"]
[null,"Boom",1559,"Rasik","Boom-5590"]
[null,"Toy",1532,"Rasik","Toy-5510"]
[null,"Other",1876,"Olga","Other-5540"]
[null,"Toy",1033,"Rasik","Toy-5510"]
[null,"Boom",1324,"Wendy","Boom-5590"]
[null,"Other",1709,"Rasik","Other-5540"]
[null,"Kite",1795,"Suix","Kite-5560"]
[null,"Boom",1657,"Megan","Boom-5590"]
[null,"Other",1434,"Olga","Other-5540"]
[null,"Boom",1824,"Debbie","Boom-5590"]
[null,"Toy",1043,"Yuliya","Toy-5510"]
[null,"Toy",1756,"Yuliya","Toy-5510"]
[null,"Kite",1023,"Igor","Kite-5560"]
[null,"Toy",1376,"Suix","Toy-5510"]
[null,"Other",1561,"Al","Other-5540"]
[null,"Boom",1665,"Rasik","Boom-5590"]
[null,"Kite",1452,"Igor","Kite-5560"]
[null,"Toy",1305,"Igor","Toy-5510"]
[null,"Toy",1965,"Igor","Toy-5510"]
[null,"Toy",1919,"Igor","Toy-5510"]
[null,"Toy",1826,"Igor","Toy-5510"]
[null,"Other",1221,"Megan","Other-5540"]
[null,"Boom",1193,"Rasik","Boom-5590"]
[null,"Other",1639,"Suix","Other-5540"]
[null,"Toy",1817,"Igor","Toy-5510"]
[null,"Other",1288,"Yuliya","Other-5540"]
[null,"Kite",1350,"Igor","Kite-5560"]
[null,"Boom",1609,"Rasik","Boom-5590"]
[null,"Other",1270,"Yuliya","Other-5540"]
[null,"Kite",1143,"Suix","Kite-5560"]
[null,"Boom",1931,"Rasik","Boom-5590"]
[null,"Toy",1707,"Debbie","Toy-5510"]
[null,"Boom",1983,"Debbie","Boom-5590"]
[null,"Boom",1910,"Debbie","Boom-5590"]
[null,"Kite",1303,"Joel","Kite-5560"]
[null,"Other",1913,"Megan","Other-5540"]
[null,"Boom",1972,"Rasik","Boom-5590"]
[null,"Boom",1253,"Al","Boom-5590"]
[null,"Kite",1034,"Olga","Kite-5560"]
[null,"Boom",1517,"Yuliya","Boom-5590"]
[null,"Kite",1071,"Rasik","Kite-5560"]
[null,"Kite",1982,"Joel","Kite-5560"]
[null,"Boom",1189,"Wendy","Boom-5590"]
[null,"Toy",1009,"Rasik","Toy-5510"]
[null,"Toy",1245,"Igor","Toy-5510"]
[null,"Other",1973,"Megan","Other-5540"]
[null,"Toy",1442,"Debbie","Toy-5510"]
[null,"Toy",1873,"Debbie","Toy-5510"]
[null,"Kite",1464,"Igor","Kite-5560"]
[null,"Other",1159,"Al","Other-5540"]
[null,"Boom",1529,"Debbie",{"$f":"B135&\"-\"&VLOOKUP(B135,G$6:H$9,2,0)","v":"Boom-5590"}]
[null,"Toy",1809,"Joel","Toy-5510"]
[null,"Other",1832,"Joel","Other-5540"]
[null,"Boom",1999,"Suix","Boom-5590"]
[null,"Other",1115,"Yuliya","Other-5540"]
[null,"Toy",1518,"Megan","Toy-5510"]
[null,"Boom",1861,"Al","Boom-5590"]
[null,"Toy",1289,"Yuliya","Toy-5510"]
[null,"Boom",1390,"Igor","Boom-5590"]
[null,"Boom",1375,"Megan","Boom-5590"]
[null,"Other",1437,"Megan","Other-5540"]
[null,"Boom",1333,"Megan","Boom-5590"]
[null,"Other",1686,"Olga","Other-5540"]
[null,"Toy",1938,"Igor","Toy-5510"]
[null,"Toy",1685,"Olga","Toy-5510"]
[null,"Kite",1947,"Suix","Kite-5560"]
[null,"Kite",1248,"Rasik","Kite-5560"]
[null,"Other",1573,"Olga","Other-5540"]
[null,"Other",1811,"Olga","Other-5540"]
[null,"Boom",1759,"Rasik","Boom-5590"]
[null,"Kite",1727,"Al","Kite-5560"]
[null,"Toy",1276,"Al","Toy-5510"]
[null,"Other",1808,"Olga","Other-5540"]
[null,"Other",1644,"Yuliya","Other-5540"]
[null,"Toy",1872,"Al","Toy-5510"]
[null,"Kite",1461,"Olga","Kite-5560"]
[null,"Boom",1564,"Wendy","Boom-5590"]
[null,"Kite",1589,"Yuliya","Kite-5560"]
[null,"Other",1989,"Olga","Other-5540"]
[null,"Toy",1194,"Igor","Toy-5510"]
[null,"Toy",1116,"Rasik","Toy-5510"]
[null,"Toy",1690,"Debbie","Toy-5510"]
[null,"Kite",1493,"Yuliya","Kite-5560"]
[null,"Boom",1680,"Megan","Boom-5590"]
[null,"Kite",1916,"Olga","Kite-5560"]
[null,"Kite",1973,"Olga","Kite-5560"]
[null,"Boom",1378,"Rasik","Boom-5590"]
[null,"Other",1182,"Olga","Other-5540"]
[null,"Toy",1252,"Yuliya","Toy-5510"]
[null,"Toy",1101,"Al","Toy-5510"]
[null,"Boom",1394,"Joel","Boom-5590"]
[null,"Other",1926,"Debbie","Other-5540"]
[null,"Toy",1379,"Debbie","Toy-5510"]
[null,"Toy",1528,"Igor","Toy-5510"]
[null,"Toy",1931,"Megan","Toy-5510"]
[null,"Boom",1780,"Igor","Boom-5590"]
[null,"Kite",1374,"Rasik","Kite-5560"]
[null,"Boom",1469,"Joel","Boom-5590"]
[null,"Kite",1066,"Yuliya","Kite-5560"]
[null,"Toy",1709,"Joel","Toy-5510"]
[null,"Other",1901,"Debbie","Other-5540"]

{"sheet":"V(5)","index":18,"n_rows":7,"n_cols":12,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["Be Careful of Formatting when you use VLOOKUP"]
["Grade Table",null,null,"Names","Percentage Grade","Letter Grade","Scores","Scores","Scores","Scores",null,"Max"]
[0.55000000000000004,"F",null,"Sioux Lim",{"$f":"SUM(G3:J3)/L$3","v":0.82250000000000001},{"$f":"VLOOKUP(E3,A$3:B$7,2)","v":"C"},52,89,90,98,null,400]
[0.65,"D",null,"Fred Snappy",{"$f":"SUM(G4:J4)/L$3","v":0.69499999999999995},{"$f":"VLOOKUP(E4,A$3:B$7,2)","v":"D"},73,83,57,65]
[0.75,"C",null,"Luong Radcoolinator",{"$f":"SUM(G5:J5)/L$3","v":0.84499999999999997},{"$f":"VLOOKUP(E5,A$3:B$7,2)","v":"C"},81,80,95,82]
[0.85,"B",null,"Mo Dan",{"$f":"SUM(G6:J6)/L$3","v":0.6825},{"$f":"VLOOKUP(E6,A$3:B$7,2)","v":"D"},67,62,69,75]
[0.95,"A"]

{"sheet":"V(5)an","index":19,"n_rows":7,"n_cols":12,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["Be Careful of Formatting when you use VLOOKUP"]
["Grade Table",null,null,"Names","Percentage Grade","Letter Grade","Scores","Scores","Scores","Scores",null,"Max"]
[0.55000000000000004,"F",null,"Sioux Lim",{"$f":"SUM(G3:J3)/L$3","v":0.82250000000000001},{"$f":"VLOOKUP(E3,A$3:B$7,2)","v":"C"},52,89,90,98,null,400]
[0.65,"D",null,"Fred Snappy",{"$f":"SUM(G4:J4)/L$3","v":0.69499999999999995},{"$f":"VLOOKUP(E4,A$3:B$7,2)","v":"D"},73,83,57,65]
[0.75,"C",null,"Luong Radcoolinator",{"$f":"SUM(G5:J5)/L$3","v":0.84499999999999997},{"$f":"VLOOKUP(E5,A$3:B$7,2)","v":"C"},81,80,95,82]
[0.85,"B",null,"Mo Dan",{"$f":"SUM(G6:J6)/L$3","v":0.6825},{"$f":"VLOOKUP(E6,A$3:B$7,2)","v":"D"},67,62,69,75]
[0.95,"A"]

{"sheet":"V(6)","index":20,"n_rows":15,"n_cols":5,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["For more on this topic (MID and VLOOKUP), see these videos:"]
["Adding 0 (zero) to a number that is a text string will convert it back to a number (MID automatically delivers a text string)"]
[]
["VLOOKUP and MID"]
["Item ID","Area",null,"Code","Area"]
["NICF-55-AM",null,null,50,"Front"]
["OCNQ-59-TP",null,null,51,"Bins"]
["KDTY-58-TQ",null,null,52,"Back Bins"]
["UGPW-52-WS",null,null,53,"Shelf"]
["XHOL-59-VD",null,null,54,"Storage Room"]
[null,null,null,55,"Freezer"]
[null,null,null,56,"Yard"]
[null,null,null,57,"Truck Port"]
[null,null,null,58,"Office"]
[null,null,null,59,"Basement"]

{"sheet":"V(6)an","index":21,"n_rows":15,"n_cols":5,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["For more on this topic (MID and VLOOKUP), see these videos:"]
["Adding 0 (zero) to a number that is a text string will convert it back to a number (MID automatically delivers a text string)"]
[]
["VLOOKUP and MID"]
["Item ID","Area",null,"Code","Area"]
["NICF-55-AM",{"$f":"VLOOKUP(MID(A6,6,2)+0,D$6:E$15,2,0)","v":"Freezer"},null,50,"Front"]
["OCNQ-59-TP",{"$f":"VLOOKUP(MID(A7,6,2)+0,D$6:E$15,2,0)","v":"Basement"},null,51,"Bins"]
["KDTY-58-TQ",{"$f":"VLOOKUP(MID(A8,6,2)+0,D$6:E$15,2,0)","v":"Office"},null,52,"Back Bins"]
["UGPW-52-WS",{"$f":"VLOOKUP(MID(A9,6,2)+0,D$6:E$15,2,0)","v":"Back Bins"},null,53,"Shelf"]
["XHOL-59-VD",{"$f":"VLOOKUP(MID(A10,6,2)+0,D$6:E$15,2,0)","v":"Basement"},null,54,"Storage Room"]
[null,null,null,55,"Freezer"]
[null,null,null,56,"Yard"]
[null,null,null,57,"Truck Port"]
[null,null,null,58,"Office"]
[null,null,null,59,"Basement"]

{"sheet":"MATCH","index":22,"n_rows":30,"n_cols":6,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["EmployeeName","Department","E-Mail","Phone"]
["Christina Kim"]
[]
[1,2,3,4,5,6]
["EmployeeName","Department","HireDate","Birthday","E-Mail","Phone"]
["Sioux Timmy","Accounting","2006-11-20","1956-02-10","Timmy@yahoo.com","206-965-7202"]
["John Pfeffer","Finance","2011-01-28","1955-03-06","Pfeffer@gmail.com","253-998-7362"]
["Fred Smith","Human Resource","2009-05-25","1956-06-18","Smith@yahoo.com","206-814-3291"]
["Ron Sabado","Accounting","2009-09-17","1963-08-06","Sabado@eif.com","206-628-9232"]
["Christina Kim","Accounting","2005-11-22","1956-04-25","Kim@gmail.com","253-631-7119"]
["Chin Pham","Finance","2005-05-01","1960-11-01","Pham@yahoo.com","206-724-9550"]
["June Sunny","Finance","2008-10-16","1959-12-16","Sunny@yahoo.com","253-995-3226"]
["Sheliadawn Bo","Human Resource","2005-10-21","1964-01-06","Bo@eif.com","206-757-8417"]
[]
[null,"Department","E-Mail","Phone"]
[]
[null,2,5,6]
[]
[]
["MATCH can be used to ask the question: \"Is item is other list?\""]
["For more about IS functions, see these videos:"]
[]
[]
["List 1",null,"List 2","Is item in List 2 in List 1?","Is item in List 2 not In list 1?"]
["Name 1",null,"Name 2"]
["Name 2",null,"Name 3"]
["Name 2",null,"Name 4"]
["Name 4",null,"Name 5"]
["Name 5",null,"Name 6"]
[null,null,null,"TRUE = This is our current customer","TRUE = This is NOT our current customer"]

{"sheet":"MATCH (2)","index":23,"n_rows":30,"n_cols":6,"rows_kept":30,"cut":false,"hidden":false,"merged":[]}
["EmployeeName","Department","E-Mail","Phone"]
["Christina Kim",{"$f":"VLOOKUP($A2,$A$6:$F$13,MATCH(B1,$A$5:$F$5,0),0)","v":"Accounting"},{"$f":"VLOOKUP($A2,$A$6:$F$13,MATCH(C1,$A$5:$F$5,0),0)","v":"Kim@gmail.com"},"253-631-7119"]
[]
[1,2,3,4,5,6]
["EmployeeName","Department","HireDate","Birthday","E-Mail","Phone"]
["Sioux Timmy","Accounting","2006-11-20","1956-02-10","Timmy@yahoo.com","206-965-7202"]
["John Pfeffer","Finance","2011-01-28","1955-03-06","Pfeffer@gmail.com","253-998-7362"]
["Fred Smith","Human Resource","2009-05-25","1956-06-18","Smith@yahoo.com","206-814-3291"]
["Ron Sabado","Accounting","2009-09-17","1963-08-06","Sabado@eif.com","206-628-9232"]
["Christina Kim","Accounting","2005-11-22","1956-04-25","Kim@gmail.com","253-631-7119"]
["Chin Pham","Finance","2005-05-01","1960-11-01","Pham@yahoo.com","206-724-9550"]
["June Sunny","Finance","2008-10-16","1959-12-16","Sunny@yahoo.com","253-995-3226"]
["Sheliadawn Bo","Human Resource","2005-10-21","1964-01-06","Bo@eif.com","206-757-8417"]
[]
[null,"Department","E-Mail","Phone"]
[null,{"$f":"MATCH(B15,$A$5:$F$5,0)","v":2},{"$f":"MATCH(C15,$A$5:$F$5,0)","v":5},6]
[null,2,5,6]
[]
[]
["MATCH can be used to ask the question: \"Is item is other list?\""]
["For more about IS functions, see these videos:"]
[]
[]
["List 1",null,"List 2","Is item in List 2 in List 1?","Is item in List 2 not In list 1?"]
["Name 1",null,"Name 2",{"$f":"ISNUMBER(MATCH($C25,$A$25:$A$29,0))","v":true},{"$f":"ISNA(MATCH($C25,$A$25:$A$29,0))","v":false}]
["Name 2",null,"Name 3",{"$f":"ISNUMBER(MATCH(C26,$A$25:$A$29,0))","v":false},{"$f":"ISNA(MATCH($C26,$A$25:$A$29,0))","v":true}]
["Name 2",null,"Name 4",true,false]
["Name 4",null,"Name 5",true,false]
["Name 5",null,"Name 6",false,true]
[null,null,null,"TRUE = This is our current customer","TRUE = This is NOT our current customer"]

{"sheet":"I(1)","index":24,"n_rows":15,"n_cols":3,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Use INDEX as a substitute for VLOOKUP when the value you want to return is to the left of the lookup column."]
["Product","Price","Description"]
["Boom01",15,"Flying Range is 10"]
["Boom02",30,"Flying Range is 20"]
["Boom03",40,"Flying Range is 50"]
["Boom04",45,"Flying Range is 60"]
["Boom05",65,"Flying Range is 70"]
["Boom06",69,"Flying Range is 80"]
["Boom07",100,"Flying Range is 85"]
["Boom08",110,"Flying Range is 110"]
["Boom09",165,"Flying Range is 160"]
[]
[null,"1-Way Lookup: Lookup Column to Left"]
[null,"Description","Product"]
[null,"Flying Range is 80"]

{"sheet":"I(1)an","index":25,"n_rows":15,"n_cols":3,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Use INDEX as a substitute for VLOOKUP when the value you want to return is to the left of the lookup column."]
["Product","Price","Description"]
["Boom01",15,"Flying Range is 10"]
["Boom02",30,"Flying Range is 20"]
["Boom03",40,"Flying Range is 50"]
["Boom04",45,"Flying Range is 60"]
["Boom05",65,"Flying Range is 70"]
["Boom06",69,"Flying Range is 80"]
["Boom07",100,"Flying Range is 85"]
["Boom08",110,"Flying Range is 110"]
["Boom09",165,"Flying Range is 160"]
[]
[null,"1-Way Lookup: Lookup Column to Left"]
[null,"Description","Product"]
[null,"Flying Range is 80",{"$f":"INDEX(A3:A11,MATCH(B15,C3:C11,0))","v":"Boom06"}]

{"sheet":"I(2)","index":26,"n_rows":25,"n_cols":8,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Use INDEX, MIN & MATCH to find vendor name with low bid"]
["Products/Vendors","Vendor1","Vendor2","Vendor3","Vendor4","Vendor5","Low Bid","Vendor for Low Bid"]
["Product1",38.99,48.6,43.53,40.08,47.92,{"$f":"MIN(B3:F3)","v":38.99}]
["Product2",57.68,31.8,52.78,31.42,55.19,31.42]
["Product3",53.32,32.64,37.69,48.29,41.59,32.64]
["Product4",35.200000000000003,40.549999999999997,32.65,36.81,41.14,32.65]
["Product5",56.72,47.16,36.42,49.56,39.25,36.42]
["Product6",47.91,35.08,51.129999999999995,49.84,42.12,35.08]
["Product7",34.81,35.11,48.629999999999995,33.32,37.83,33.32]
["Product8",42.25,35.76,58.6,46.28,40.53,35.76]
["Product9",40.14,42.31,37.619999999999997,59.97,42.57,37.619999999999997]
["Product10",36.480000000000004,40.79,53.239999999999995,51.010000000000005,51.239999999999995,36.480000000000004]
["Product11",38.57,40.06,54.71,39.700000000000003,54.730000000000004,38.57]
["Product12",52.66,43.61,59.980000000000004,34.61,52.65,34.61]
[]
["Use INDEX, MAX & MATCH to find student with max score"]
["Name","Score",null,"Max","Lookup Name w Max Score"]
["Student 1",53,null,null,null,null,"<== lookup name w max score"]
["Student 2",18]
["Student 3",99]
["Student 4",45]
["Student 5",19]
["Student 6",27]
["Student 7",85]
["Student 8",0]

{"sheet":"I(2)an","index":27,"n_rows":25,"n_cols":8,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Use INDEX, MIN & MATCH to find vendor name with low bid"]
["Products/Vendors","Vendor1","Vendor2","Vendor3","Vendor4","Vendor5","Low Bid","Vendor for Low Bid"]
["Product1",38.99,48.6,43.53,40.08,47.92,{"$f":"MIN(B3:F3)","v":38.99},{"$f":"INDEX(B$2:F$2,MATCH(G3,B3:F3,0))","v":"Vendor1"}]
["Product2",57.68,31.8,52.78,31.42,55.19,31.42,{"$f":"INDEX(B$2:F$2,MATCH(G4,B4:F4,0))","v":"Vendor4"}]
["Product3",53.32,32.64,37.69,48.29,41.59,32.64,"Vendor2"]
["Product4",35.200000000000003,40.549999999999997,32.65,36.81,41.14,32.65,"Vendor3"]
["Product5",56.72,47.16,36.42,49.56,39.25,36.42,"Vendor3"]
["Product6",47.91,35.08,51.129999999999995,49.84,42.12,35.08,"Vendor2"]
["Product7",34.81,35.11,48.629999999999995,33.32,37.83,33.32,"Vendor4"]
["Product8",42.25,35.76,58.6,46.28,40.53,35.76,"Vendor2"]
["Product9",40.14,42.31,37.619999999999997,59.97,42.57,37.619999999999997,"Vendor3"]
["Product10",36.480000000000004,40.79,53.239999999999995,51.010000000000005,51.239999999999995,36.480000000000004,"Vendor1"]
["Product11",38.57,40.06,54.71,39.700000000000003,54.730000000000004,38.57,"Vendor1"]
["Product12",52.66,43.61,59.980000000000004,34.61,52.65,34.61,"Vendor4"]
[]
["Use INDEX, MAX & MATCH to find student with max score"]
["Name","Score",null,"Max","Lookup Name w Max Score"]
["Student 1",53,null,{"$f":"MAX(B18:B25)","v":99},{"$f":"INDEX(A18:A25,MATCH(D18,B18:B25,0))","v":"Student 3"},null,"<== lookup name w max score"]
["Student 2",18]
["Student 3",99]
["Student 4",45]
["Student 5",19]
["Student 6",27]
["Student 7",85]
["Student 8",0]

{"sheet":"I(3)","index":28,"n_rows":7,"n_cols":3,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["Use INDEX and RANDBETWEEN to randomly select a name"]
["Names",null,"Name Selected:"]
["Sioux"]
["Dave"]
["Isaac"]
["Sue"]
["Luong"]

{"sheet":"I(3)an","index":29,"n_rows":7,"n_cols":3,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
["Use INDEX and RANDBETWEEN to randomly select a name"]
["Names",null,"Name Selected:"]
["Sioux",null,{"$f":"INDEX(A3:A7,RANDBETWEEN(1,5))","v":"Isaac"}]
["Dave"]
["Isaac"]
["Sue"]
["Luong"]

{"sheet":"I(4)","index":30,"n_rows":17,"n_cols":4,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Use INDEX, MATCH and SUM to retrieve a whole column of values and add them"]
["0 (zero) in row_num argument will return a whole column of values (“all rows” or “entire column”)"]
["Month to add",{"$f":"A4&\" Total\"","v":"Mar Total"}]
["Mar"]
["Jan","Feb","Mar","Apr"]
[1,4,4,5]
[3,4,12,1]
[1,4,2,2]
[]
["Use INDEX, MATCH and SUM to retrieve a whole row of values and add them"]
["0 (zero) in column_num argument will return a whole row of values (“all columns” or “entire row”)"]
["Month to add",{"$f":"A13&\" Total\"","v":"Jan Total"}]
["Jan"]
[{"$f":"TRANSPOSE(A5:D8)","v":"Jan"},1,3,1]
["Feb",4,4,4]
["Mar",4,12,2]
["Apr",5,1,2]

{"sheet":"I(4)an","index":31,"n_rows":17,"n_cols":4,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Use INDEX, MATCH and SUM to retrieve a whole column of values and add them"]
["0 (zero) in row_num argument will return a whole column of values (“all rows” or “entire column”)"]
["Month to add",{"$f":"A4&\" Total\"","v":"Mar Total"}]
["Mar",{"$f":"SUM(INDEX(A6:D8,0,MATCH(A4,A5:D5,0)))","v":18}]
["Jan","Feb","Mar","Apr"]
[1,4,4,5]
[3,4,12,1]
[1,4,2,2]
[]
["Use INDEX, MATCH and SUM to retrieve a whole row of values and add them"]
["0 (zero) in column_num argument will return a whole row of values (“all columns” or “entire row”)"]
["Month to add",{"$f":"A13&\" Total\"","v":"Jan Total"}]
["Jan",{"$f":"SUM(INDEX(B14:D17,MATCH(A13,A14:A17,0),0))","v":5}]
[{"$f":"TRANSPOSE(A5:D8)","v":"Jan"},1,3,1]
["Feb",4,4,4]
["Mar",4,12,2]
["Apr",5,1,2]

{"sheet":"I(5)","index":32,"n_rows":11,"n_cols":10,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Score","Decimal Grade",null,null,null,null,"ROWS from 6 to 1 big # to small",null,"ROWS from 1 to 6 small # to big"]
[450,4,null,null,null,null,{"$f":"ROWS(A2:A$7)","v":6}," =ROWS(A2:A$7)",{"$f":"ROWS(I$2:I2)","v":1}," =ROWS(I$2:I2)"]
[425,3.7,null,null,null,null,{"$f":"ROWS(A3:A$7)","v":5}," =ROWS(A3:A$7)",{"$f":"ROWS(I$2:I3)","v":2}," =ROWS(I$2:I3)"]
[350,3,null,null,null,null,{"$f":"ROWS(A4:A$7)","v":4}," =ROWS(A4:A$7)",{"$f":"ROWS(I$2:I4)","v":3}," =ROWS(I$2:I4)"]
[300,2.5,null,null,null,null,{"$f":"ROWS(A5:A$7)","v":3}," =ROWS(A5:A$7)",{"$f":"ROWS(I$2:I5)","v":4}," =ROWS(I$2:I5)"]
[270,2,null,null,null,null,{"$f":"ROWS(A6:A$7)","v":2}," =ROWS(A6:A$7)",{"$f":"ROWS(I$2:I6)","v":5}," =ROWS(I$2:I6)"]
[0,0,null,null,null,null,{"$f":"ROWS(A7:A$7)","v":1}," =ROWS(A7:A$7)",{"$f":"ROWS(I$2:I7)","v":6}," =ROWS(I$2:I7)"]
[]
["Use INDEX to flip (invert) a range 180 degrees:\n=INDEX(A$2:A$7,ROWS(A2:A$7))"]
["Score","Decimal Grade",null,"Score","Decimal Grade"]
[null,null,null,401,{"$f":"VLOOKUP(D11,A11:B16,2)","v":{"$error":"#N/A"}}]

{"sheet":"I(5)an","index":33,"n_rows":16,"n_cols":10,"rows_kept":16,"cut":false,"hidden":false,"merged":[]}
["Score","Decimal Grade",null,null,null,null,"ROWS from 6 to 1 big # to small",null,"ROWS from 1 to 6 small # to big"]
[450,4,null,null,null,null,{"$f":"ROWS(A2:A$7)","v":6}," =ROWS(A2:A$7)",{"$f":"ROWS(I$2:I2)","v":1}," =ROWS(I$2:I2)"]
[425,3.7,null,null,null,null,{"$f":"ROWS(A3:A$7)","v":5}," =ROWS(A3:A$7)",{"$f":"ROWS(I$2:I3)","v":2}," =ROWS(I$2:I3)"]
[350,3,null,null,null,null,{"$f":"ROWS(A4:A$7)","v":4}," =ROWS(A4:A$7)",{"$f":"ROWS(I$2:I4)","v":3}," =ROWS(I$2:I4)"]
[300,2.5,null,null,null,null,{"$f":"ROWS(A5:A$7)","v":3}," =ROWS(A5:A$7)",{"$f":"ROWS(I$2:I5)","v":4}," =ROWS(I$2:I5)"]
[270,2,null,null,null,null,{"$f":"ROWS(A6:A$7)","v":2}," =ROWS(A6:A$7)",{"$f":"ROWS(I$2:I6)","v":5}," =ROWS(I$2:I6)"]
[0,0,null,null,null,null,{"$f":"ROWS(A7:A$7)","v":1}," =ROWS(A7:A$7)",{"$f":"ROWS(I$2:I7)","v":6}," =ROWS(I$2:I7)"]
[]
["Use INDEX to flip (invert) a range 180 degrees:\n=INDEX(A$2:A$7,ROWS(A2:A$7))"]
["Score","Decimal Grade",null,"Score","Decimal Grade"]
[{"$f":"INDEX(A$2:A$7,ROWS(A2:A$7))","v":0},{"$f":"INDEX(B$2:B$7,ROWS(B2:B$7))","v":0},null,401,{"$f":"VLOOKUP(D11,A11:B16,2)","v":3}]
[{"$f":"INDEX(A$2:A$7,ROWS(A3:A$7))","v":270},{"$f":"INDEX(B$2:B$7,ROWS(B3:B$7))","v":2}]
[{"$f":"INDEX(A$2:A$7,ROWS(A4:A$7))","v":300},{"$f":"INDEX(B$2:B$7,ROWS(B4:B$7))","v":2.5}]
[{"$f":"INDEX(A$2:A$7,ROWS(A5:A$7))","v":350},{"$f":"INDEX(B$2:B$7,ROWS(B5:B$7))","v":3}]
[{"$f":"INDEX(A$2:A$7,ROWS(A6:A$7))","v":425},{"$f":"INDEX(B$2:B$7,ROWS(B6:B$7))","v":3.7}]
[{"$f":"INDEX(A$2:A$7,ROWS(A7:A$7))","v":450},{"$f":"INDEX(B$2:B$7,ROWS(B7:B$7))","v":4}]

{"sheet":"I(6)","index":34,"n_rows":13,"n_cols":3,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["Use INDEX & MATCH to find how much to get to next bracket"]
["Sales","Commission Paid"]
[5555,{"$f":"INDEX(A7:A13,MATCH(A3,A7:A13)+1)-A3","v":4445}]
[]
["Commission Table"]
["Sales","Rate","Commission from Previous Bracket"]
[0,0]
[5000,5.0000000000000001E-3,0]
[10000,0.01,{"$f":"(A9-A8)*B8+C8","v":25}]
[20000,0.02,{"$f":"(A10-A9)*B9+C9","v":125}]
[30000,0.03,{"$f":"(A11-A10)*B10+C10","v":325}]
[50000,0.06,{"$f":"(A12-A11)*B11+C11","v":925}]
[100000,0.1,{"$f":"(A13-A12)*B12+C12","v":3925}]

{"sheet":"I(6)an","index":35,"n_rows":13,"n_cols":3,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["Use INDEX & MATCH to find how much to get to next bracket"]
["Sales","Commission Paid"]
[5555,{"$f":"INDEX(A7:A13,MATCH(A3,A7:A13)+1)-A3","v":4445}]
[]
["Commission Table"]
["Sales","Rate","Commission from Previous Bracket"]
[0,0]
[5000,5.0000000000000001E-3,0]
[10000,0.01,{"$f":"(A9-A8)*B8+C8","v":25}]
[20000,0.02,{"$f":"(A10-A9)*B9+C9","v":125}]
[30000,0.03,{"$f":"(A11-A10)*B10+C10","v":325}]
[50000,0.06,{"$f":"(A12-A11)*B11+C11","v":925}]
[100000,0.1,{"$f":"(A13-A12)*B12+C12","v":3925}]

{"sheet":"I(7)","index":36,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"CHOOSE","index":37,"n_rows":25,"n_cols":12,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Product 1",null,null,null,"Pro","Amount of Sale","Table","Discount ?",null,null,"Pro","Amount of Sale"]
[0,0,null,null,"Product 1",3225,null,null,null,null,{"$f":"\"Product \"&RANDBETWEEN(1,3)","v":"Product 3"},{"$f":"RANDBETWEEN(100,10000)","v":2514}]
[500,0.01,null,null,"Product 3",2014]
[1500,0.02,null,null,"Product 1",1264]
[5000,0.03,null,null,"Product 1",1755]
[null,null,null,null,"Product 3",4609]
[null,null,null,null,"Product 3",9300]
[null,null,null,null,"Product 3",8850]
["Product 2",null,null,null,"Product 1",2641]
[0,0,null,null,"Product 1",8028]
[1000,1.4999999999999999E-2,null,null,"Product 1",3288]
[2000,2.5000000000000001E-2,null,null,"Product 2",1434]
[8000,0.04,null,null,"Product 1",2295]
[null,null,null,null,"Product 2",3808]
[null,null,null,null,"Product 2",8949]
[null,null,null,null,"Product 2",489]
["Product 3",null,null,null,"Product 1",7984]
[0,0,null,null,"Product 1",9610]
[250,5.0000000000000001E-3,null,null,"Product 1",5376]
[1000,0.01,null,null,"Product 3",5596]
[3000,1.4999999999999999E-2]
[]
["Product 1",1]
["Product 2",2]
["Product 3",3]

{"sheet":"CHOOSE (2)","index":38,"n_rows":25,"n_cols":13,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Product 1",null,null,null,"Pro","Amount of Sale","Table","Discount ?",null,null,null,"Pro","Amount of Sale"]
[0,0,null,null,"Product 1",3225,{"$f":"VLOOKUP(E2,$A$23:$B$25,2,0)","v":1},{"$f":"F2*VLOOKUP(F2,CHOOSE(G2,$A$2:$B$5,$A$10:$B$13,$A$18:$B$21),2)","v":64.5},null,{"$f":"F2*VLOOKUP(F2,CHOOSE(VLOOKUP(E2,$A$23:$B$25,2,0),$A$2:$B$5,$A$10:$B$13,$A$18:$B$21),2)","v":64.5},null,{"$f":"\"Product \"&RANDBETWEEN(1,3)","v":"Product 3"},{"$f":"RANDBETWEEN(100,10000)","v":623}]
[500,0.01,null,null,"Product 3",2014,3,{"$f":"F3*VLOOKUP(F3,CHOOSE(G3,$A$2:$B$5,$A$10:$B$13,$A$18:$B$21),2)","v":20.14},null,{"$f":"F3*VLOOKUP(F3,CHOOSE(VLOOKUP(E3,$A$23:$B$25,2,0),$A$2:$B$5,$A$10:$B$13,$A$18:$B$21),2)","v":20.14}]
[1500,0.02,null,null,"Product 1",1264,1,12.64,null,12.64]
[5000,0.03,null,null,"Product 1",1755,1,35.1,null,35.1]
[null,null,null,null,"Product 3",4609,3,69.134999999999991,null,69.134999999999991]
[null,null,null,null,"Product 3",9300,3,139.5,null,139.5]
[null,null,null,null,"Product 3",8850,3,132.75,null,132.75]
["Product 2",null,null,null,"Product 1",2641,1,52.82,null,52.82]
[0,0,null,null,"Product 1",8028,1,240.84,null,240.84]
[1000,1.4999999999999999E-2,null,null,"Product 1",3288,1,65.760000000000005,null,65.760000000000005]
[2000,2.5000000000000001E-2,null,null,"Product 2",1434,2,21.509999999999998,null,21.509999999999998]
[8000,0.04,null,null,"Product 1",2295,1,45.9,null,45.9]
[null,null,null,null,"Product 2",3808,2,95.2,null,95.2]
[null,null,null,null,"Product 2",8949,2,357.96,null,357.96]
[null,null,null,null,"Product 2",489,2,0,null,0]
["Product 3",null,null,null,"Product 1",7984,1,239.51999999999998,null,239.51999999999998]
[0,0,null,null,"Product 1",9610,1,288.3,null,288.3]
[250,5.0000000000000001E-3,null,null,"Product 1",5376,1,161.28,null,161.28]
[1000,0.01,null,null,"Product 3",5596,3,83.94,null,83.94]
[3000,1.4999999999999999E-2]
[]
["Product 1",1]
["Product 2",2]
["Product 3",3]