{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Question","Try","Mr Excel","excelisfun","Array","Question (2)","XLarium","ExcelYourselfPL"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":5,"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":"Question","index":0,"n_rows":19,"n_cols":14,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 1","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,null,2,2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 2","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.25]
[null,null,"# of Goals Met",null,null,null,null,null,null,null,null,null,1,0.5]
[null,"Cat 1","Cat 2","Cat 3",null,"Score",null,null,null,null,null,null,2,1]
["Andy",0,5,2,null,{"$f":"VLOOKUP(B12,$M$4:$N$6,2,0)*VLOOKUP(C12,$M$9:$N$14,2,0)*VLOOKUP(D12,$M$17:$N$19,2,0)","v":2},null,null,null,null,null,null,3,1.25]
["Bill",1,1,1,null,{"$f":"VLOOKUP(B13,$M$4:$N$6,2,0)*VLOOKUP(C13,$M$9:$N$14,2,0)*VLOOKUP(D13,$M$17:$N$19,2,0)","v":0.5},null,null,null,null,null,null,4,1.5]
["Claire",2,2,2,null,{"$f":"VLOOKUP(B14,$M$4:$N$6,2,0)*VLOOKUP(C14,$M$9:$N$14,2,0)*VLOOKUP(D14,$M$17:$N$19,2,0)","v":4},null,null,null,null,null,null,5,2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 3","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,null,2,2]

{"sheet":"Try","index":1,"n_rows":19,"n_cols":14,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 1","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,null,2,2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 2","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.25]
[null,null,"# of Goals Met",null,null,null,null,null,null,null,null,null,1,0.5]
["Name","Cat 1","Cat 2","Cat 3",null,"Score",null,null,null,null,null,null,2,1]
["Andy",0,5,2,null,{"$f":"VLOOKUP(B12,$M$4:$N$6,2,0)*VLOOKUP(C12,$M$9:$N$14,2,0)*VLOOKUP(D12,$M$17:$N$19,2,0)","v":2},null,null,null,null,null,null,3,1.25]
["Bill",1,1,1,null,{"$f":"VLOOKUP(B13,$M$4:$N$6,2,0)*VLOOKUP(C13,$M$9:$N$14,2,0)*VLOOKUP(D13,$M$17:$N$19,2,0)","v":0.5},null,null,null,null,null,null,4,1.5]
["Claire",2,2,2,null,{"$f":"VLOOKUP(B14,$M$4:$N$6,2,0)*VLOOKUP(C14,$M$9:$N$14,2,0)*VLOOKUP(D14,$M$17:$N$19,2,0)","v":4},null,null,null,null,null,null,5,2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 3","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,null,2,2]

{"sheet":"Mr Excel","index":2,"n_rows":19,"n_cols":14,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 1","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,null,2,2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 2","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.25]
[null,null,"# of Goals Met",null,null,null,null,null,null,null,null,null,1,0.5]
[null,"Cat 1","Cat 2","Cat 3",null,"Score",null,null,null,null,null,null,2,1]
["Andy",0,5,2,null,{"$f":"VLOOKUP(B12,$M$4:$N$6,2,0)*VLOOKUP(C12,$M$9:$N$14,2,0)*VLOOKUP(D12,$M$17:$N$19,2,0)","v":2},null,null,null,null,null,null,3,1.25]
["Bill",1,1,1,null,{"$f":"VLOOKUP(B13,$M$4:$N$6,2,0)*VLOOKUP(C13,$M$9:$N$14,2,0)*VLOOKUP(D13,$M$17:$N$19,2,0)","v":0.5},null,null,null,null,null,null,4,1.5]
["Claire",2,2,2,null,{"$f":"VLOOKUP(B14,$M$4:$N$6,2,0)*VLOOKUP(C14,$M$9:$N$14,2,0)*VLOOKUP(D14,$M$17:$N$19,2,0)","v":4},null,null,null,null,null,null,5,2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 3","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,null,2,2]

{"sheet":"excelisfun","index":3,"n_rows":17,"n_cols":11,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,null,null,null,"Category 1","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,2,2]
[]
[null,null,null,null,null,null,null,null,"Category 2","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,0,0.25]
[null,null,null,null,null,null,null,null,null,1,0.5]
[null,null,null,null,null,null,null,null,null,2,1]
[null,"# of Goals Met",null,null,null,null,null,null,null,3,1.25]
["Name","Cat 1","Cat 2","Cat 3","Score",null,null,null,null,4,1.5]
["Andy",0,5,2,{"$f":"LOOKUP(B12,$J$2:$K$4)*LOOKUP(C12,$J$7:$K$12)*LOOKUP(D12,$J$15:$K$17)","v":2},{"$f":"LOOKUP(B12:D12,(J2:K4,J7:K12,J15:K17))","v":{"$error":"#VALUE!"}},null,null,null,5,2]
["Bill",1,1,1,{"$f":"LOOKUP(B13,$J$2:$K$4)*LOOKUP(C13,$J$7:$K$12)*LOOKUP(D13,$J$15:$K$17)","v":0.5}]
["Claire",2,2,2,{"$f":"LOOKUP(B14,$J$2:$K$4)*LOOKUP(C14,$J$7:$K$12)*LOOKUP(D14,$J$15:$K$17)","v":4},null,null,null,"Category 3","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,2,2]

{"sheet":"Array","index":4,"n_rows":41,"n_cols":22,"rows_kept":41,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[null,null,"Cat 1","Cat 2","Cat 3","Score"]
[null,null,0,5,2,{"$f":"PRODUCT(LOOKUP(C6:E6,$H$6:$I$11))","v":2.25},null,0,0.5]
[null,null,0,1,1,{"$f":"PRODUCT(LOOKUP(C7:E7,$H$6:$I$11))","v":0.5},null,1,1]
[null,null,2,3,2,{"$f":"PRODUCT(LOOKUP(C8:E8,$H$6:$I$11))","v":3.9375},null,2,1.5]
[null,null,null,null,null,null,null,3,1.75]
[null,null,null,null,null,null,null,4,2]
[null,null,null,null,null,null,null,5,3]
[]
[null,"Sales Rep","Product","Region","Sales",null,"Sales Rep","Region",null,null,null,null,null,null,null,null,"Date","Product","Sales",null,"Criteria","Last"]
[null,"Jo","Pro 2","W",2,null,"Jo","W",null,null,null,null,null,null,null,null,"2013-09-26","A",1,null,"B",{"$f":"LOOKUP(2,1/(R14:R19=U14),S14:S19)","v":5}]
[null,"Mo","Pro 1","W",1,null,"Mo","E",null,null,null,null,null,null,null,null,"2013-09-27","B",3]
[null,"Phil","Pro 1","W",2,null,null,null,null,null,null,null,null,null,null,null,"2013-09-28","A",1]
[null,"Mo","Pro 1","E",2,null,"Example 1",null,"Example 2",null,"Sales",null,"Sales",null,null,null,"2013-09-29","B",3]
[null,"Jo","Pro 1","E",1,null,"Total",null,"Add 2 Biggest",null,1,null,5,null,null,null,"2013-09-30","B",5]
[null,"Jo","Pro 1","W",1,null,{"$f":"SUMPRODUCT(SUMIFS(E14:E21,D14:D21,H14:H15,B14:B21,G14:G15))","v":5},null,{"$f":"SUM(LARGE((K18:K23,M18:M23),{1,2}))","v":16},null,2,null,7,null,null,null,"2013-10-01","A",3]
[null,"Phil","Pro 1","W",2,null,{"$f":"DSUM(B13:E21,E13,G13:H15)","v":5},null,null,null,3,null,9]
[null,"Phil","Pro 1","E",2,null,null,null,null,null,2,null,1]
[null,null,null,null,null,null,"Product","Total",null,null,5,null,3]
[null,null,null,null,null,null,"Pro 2",{"$f":"SUMPRODUCT(SUMIFS($E$14:$E$21,$D$14:$D$21,$H$14:$H$15,$B$14:$B$21,$G$14:$G$15,$C$14:$C$21,G23))","v":2},null,null,2,null,4]
[null,null,null,null,null,null,"Pro 1",{"$f":"SUMPRODUCT(SUMIFS($E$14:$E$21,$D$14:$D$21,$H$14:$H$15,$B$14:$B$21,$G$14:$G$15,$C$14:$C$21,G24))","v":3}]
[null,null,null,null,null,null,null,null,null,null,"Category 1","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,2,2]
[null,null,null,"# of Goals Met"]
[null,null,"Name","Cat 1","Cat 2","Cat 3","Score",null,null,null,"Category 2","# of Goals","Mult Factor"]
[null,null,"Andy",0,5,2,{"$f":"LOOKUP(D31,$L$26:$M$28)*LOOKUP(E31,$L$31:$M$36)*LOOKUP(F31,$L$39:$M$41)","v":2},{"$f":"LOOKUP(D31:F31,(L26:M28,L31:M36,L39:M41))","v":{"$error":"#N/A"}},null,null,null,0,0.25]
[null,null,"Bill",1,1,1,{"$f":"LOOKUP(D32,$L$26:$M$28)*LOOKUP(E32,$L$31:$M$36)*LOOKUP(F32,$L$39:$M$41)","v":0.5},null,null,null,null,1,0.5]
[null,null,"Claire",2,2,2,{"$f":"LOOKUP(D33,$L$26:$M$28)*LOOKUP(E33,$L$31:$M$36)*LOOKUP(F33,$L$39:$M$41)","v":4},null,null,null,null,2,1]
[null,null,null,null,null,null,null,null,null,null,null,3,1.25]
[null,null,null,null,null,null,null,null,null,null,null,4,1.5]
[null,null,null,null,null,null,null,null,null,null,null,5,2]
[]
[null,null,null,null,null,null,null,null,null,null,"Category 3","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,2,2]

{"sheet":"Question (2)","index":5,"n_rows":19,"n_cols":14,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 1","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,null,2,2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 2","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.25]
[null,null,"# of Goals Met",null,null,null,null,null,null,null,null,null,1,0.5]
[null,"Cat 1","Cat 2","Cat 3",null,"Score","Score",null,null,null,null,null,2,1]
["Andy",0,5,2,null,{"$f":"VLOOKUP(B12,$M$4:$N$6,2,0)*VLOOKUP(C12,$M$9:$N$14,2,0)*VLOOKUP(D12,$M$17:$N$19,2,0)","v":2},{"$f":"MF(B12:D12,$M$4:$N$6,$M$9:$N$14,$M$17:$N$19)","v":2},null,null,null,null,null,3,1.25]
["Bill",1,1,1,null,{"$f":"VLOOKUP(B13,$M$4:$N$6,2,0)*VLOOKUP(C13,$M$9:$N$14,2,0)*VLOOKUP(D13,$M$17:$N$19,2,0)","v":0.5},{"$f":"MF(B13:D13,$M$4:$N$6,$M$9:$N$14,$M$17:$N$19)","v":0.5},null,null,null,null,null,4,1.5]
["Claire",2,2,2,null,{"$f":"VLOOKUP(B14,$M$4:$N$6,2,0)*VLOOKUP(C14,$M$9:$N$14,2,0)*VLOOKUP(D14,$M$17:$N$19,2,0)","v":4},{"$f":"MF(B14:D14,$M$4:$N$6,$M$9:$N$14,$M$17:$N$19)","v":4},null,null,null,null,null,5,2]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Category 3","# of Goals","Mult Factor"]
[null,null,null,null,null,null,null,null,null,null,null,null,0,0.5]
[null,null,null,null,null,null,null,null,null,null,null,null,1,1]
[null,null,null,null,null,null,null,null,null,null,null,null,2,2]

{"sheet":"XLarium","index":6,"n_rows":13,"n_cols":9,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
[null,"# of Goals Met",null,null,null,null,"Cat","# of Goals","Mult Factor"]
["Name","Cat 1","Cat 2","Cat 3","Xlarium solution",null,"Cat 1",0,0.5]
["Andy",0,5,2,{"$f":"PRODUCT(IF($G$2:$G$13=$B$2:$D$2,IF($H$2:$H$13=B3:D3,$I$2:$I$13)))","v":2},null,"Cat 1",1,1]
["Bill",1,1,1,{"$f":"PRODUCT(IF($G$2:$G$13=$B$2:$D$2,IF($H$2:$H$13=B4:D4,$I$2:$I$13)))","v":0.5},null,"Cat 1",2,2]
["Claire",2,2,2,{"$f":"PRODUCT(IF($G$2:$G$13=$B$2:$D$2,IF($H$2:$H$13=B5:D5,$I$2:$I$13)))","v":4},null,"Cat 2",0,0.25]
[null,null,null,null,null,null,"Cat 2",1,0.5]
[null,null,null,null,null,null,"Cat 2",2,1]
[null,null,null,null,null,null,"Cat 2",3,1.25]
[null,null,null,null,null,null,"Cat 2",4,1.5]
[null,null,null,null,null,null,"Cat 2",5,2]
[null,null,null,null,null,null,"Cat 3",0,0.5]
[null,null,null,null,null,null,"Cat 3",1,1]
[null,null,null,null,null,null,"Cat 3",2,2]

{"sheet":"ExcelYourselfPL","index":7,"n_rows":24,"n_cols":8,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[null,"Video Response To Mr Excel & excelisfun Trick 141"]
[null,"Video Response To Mr Excel & excelisfun Trick 141"]
[null,"http://www.youtube.com/watch?v=fcUv4yjm2Sc"]
[null,null,null,null,null,"\"Differencing\" or \"container\" functions."]
[null,null,null,null,null,null,null,"N for numbers"]
[null,"# of Goals Met",null,null,null,null,null,"T for text"]
["Name","Cat 1","Cat 2","Cat 3","ExcelYourselfPL","ExcelYourselfPL w TRANSPOSE","SUBTOTAL instead of TRANSPOSE","N function"]
["Andy",0,5,2,{"$f":"PRODUCT(INDIRECT(ADDRESS(MATCH(B12:D12,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17))))","v":0.5},{"$f":"PRODUCT(TRANSPOSE(INDIRECT(ADDRESS(MATCH(B12:D12,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":2},{"$f":"PRODUCT(SUBTOTAL(9,INDIRECT(ADDRESS(MATCH(B12:D12,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":2},{"$f":"PRODUCT(N(INDIRECT(ADDRESS(MATCH(B12:D12,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":2}]
["Bill",1,1,1,{"$f":"PRODUCT(INDIRECT(ADDRESS(MATCH(B13:D13,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17))))","v":1},{"$f":"PRODUCT(TRANSPOSE(INDIRECT(ADDRESS(MATCH(B13:D13,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":0.5},{"$f":"PRODUCT(SUBTOTAL(9,INDIRECT(ADDRESS(MATCH(B13:D13,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":0.5},{"$f":"PRODUCT(N(INDIRECT(ADDRESS(MATCH(B13:D13,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":0.5}]
["Claire",2,2,2,{"$f":"PRODUCT(INDIRECT(ADDRESS(MATCH(B14:D14,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17))))","v":2},{"$f":"PRODUCT(TRANSPOSE(INDIRECT(ADDRESS(MATCH(B14:D14,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":4},{"$f":"PRODUCT(SUBTOTAL(9,INDIRECT(ADDRESS(MATCH(B14:D14,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":4},{"$f":"PRODUCT(N(INDIRECT(ADDRESS(MATCH(B14:D14,$A$18:$A$23,0)+ROW($A$17),COLUMN($B$17:$D$17)))))","v":4}]
[]
[null,null,null,null,null,"Without Transpose INDIRECT gives Product only the 1st reference."]
["# of Goals","Cat 1","Cat 2","Cat 3"]
[0,0.5,0.25,0.5,null,"What?"]
[1,1,0.5,1,null,"Maybe:"]
[2,2,1,2,null,"N or TRANSPOSE or SUBTOTAL \"dereference\" the references craeted by INDIRECT."]
[3,null,1.25,null,null,"Other situations where TRANSPOSE or SUBTOTAL \"dereference:"]
[4,null,1.5,null,null,"Excel Magic Trick 735: Find Max Running Three Week Totals SUBTOTAL / OFFSET Array Formula "]
[5,null,2,null,null,"http://www.youtube.com/watch?v=o325ACwuRgo"]
[null,null,null,null,null,"But, not sure 100% why we must use TRANSPOSE or SUBTOTAL…"]