{"text_format":"1","workbook":"xlsx","n_sheets":11,"sheets":["Contents","array","count errors","filter","offset","top n","form control","vlookup","xlookup","Sheet11","Sample"],"not_rendered":{"charts":2,"images":0,"macros":1,"external_links":1,"defined_names":4,"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":"Contents","index":0,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
[null,"Table of Contents"]
[]
[null,1,"array"]
[null,2,"count errors"]
[null,3,"filter"]
[null,4,"form control"]
[null,5,"offset"]
[null,6,"Sheet11"]
[null,7,"top n"]
[null,8,"vlookup"]
[null,9,"xlookup"]

{"sheet":"array","index":1,"n_rows":15,"n_cols":6,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["WRITE ONE FORMULA"]
[null,null,"DYNAMIC ARRAYS"]
[null,null,"https://support.microsoft.com/en-us/office/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2"]
[]
[]
[null,null,"Target"]
[null,"PY SALES",0.1,0.12,0.14000000000000001,0.16]
["Darren Powers",21000,{"$f":"B8:B15*(1+C7:F7)","v":23100.000000000004},23520.000000000004,23940.000000000004,24360]
["Phillina Ober",46000,50600.000000000007,51520.000000000007,52440.000000000007,53359.999999999993]
["Mick Brown",19000,20900,21280.000000000004,21660.000000000004,22040]
["Jack O'Briant",9000,9900,10080.000000000002,10260.000000000002,10440]
["Lycoris Saunders",10000,11000,11200.000000000002,11400.000000000002,11600]
["Maria Etezadi",108000,118800.00000000001,120960.00000000001,123120.00000000001,125279.99999999999]
["Vivek Sundaresam",20000,22000,22400.000000000004,22800.000000000004,23200]
["Melanie Seite",14000,15400.000000000002,15680.000000000002,15960.000000000002,16239.999999999998]

{"sheet":"count errors","index":2,"n_rows":12,"n_cols":13,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Q1","Q2","Q3","Q4",null,null,"count blank",{"$f":"COUNTBLANK(B3:E12)","v":4},null,null,null,"UNARY OPERATOR"]
[null,183,118,133,105,null,null,null,null,null,null,null,"https://exceljet.net/glossary/double-unary"]
[null,125,null,180,113,null,null,"count errors",{"$f":"SUM(IF(ISERROR(B3:E12)=TRUE, 1, 0))","v":4},null,"CTRL SHIFT ENTER",null,"OLF VERSIONS OF EXCEL"]
[null,186,162,134,179]
[null,null,158,{"$f":"8/0","v":{"$error":"#DIV/0!"}},155,null,null,"count errors",{"$f":"SUM(--ISERROR(B3:E12))","v":4}]
[null,179,155,150,{"$f":"8/0","v":{"$error":"#DIV/0!"}}]
[null,{"$f":"8/0","v":{"$error":"#DIV/0!"}},180,149,140]
[null,163,198,159,{"$f":"8/0","v":{"$error":"#DIV/0!"}}]
[null,156,176,null,118]
[null,168,143,194]
[null,194,137,185,175]

{"sheet":"filter","index":3,"n_rows":28,"n_cols":13,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["EXTRACT CUSTOMERS:",null,null,null,"SHIP MODE"]
[null,null,null,null,"FIRST CLASS"]
[]
[]
[]
[null,null,null,null,"SALES","FROM",300]
[null,null,null,null,null,"TO",1000]
[]
[]
["Ship Mode","Customer Name","Sum of Sales",null,"Ship Mode","Customer Name","Sum of Sales"]
["Standard Class","Darren Powers",291,null,{"$f":"_xlfn._xlws.FILTER(A11:C28,\n\n(A11:A28=E2) * (C11:C28>=G6) * (C11:C28<=G7))","v":"First Class"},"Mick Brown",390,null,{"$f":"A11:A28=E2","v":false},{"$f":"C11:C28>=G6","v":false},{"$f":"C11:C28<=G7","v":true},null,{"$f":"I11*J11*K11","v":0}]
["Standard Class","Phillina Ober",411,null,null,null,null,null,false,true,true,null,{"$f":"I12*J12*K12","v":0}]
["Standard Class","Mick Brown",334,null,null,null,null,null,false,true,true,null,0]
["First Class","Jack O'Briant",264,null,null,null,null,null,true,false,true,null,0]
["Second Class","Lycoris Saunders",206,null,null,null,null,null,false,false,true,null,0]
["Standard Class","Maria Etezadi",332,null,null,null,null,null,false,true,true,null,0]
["Standard Class","Darren Powers",396,null,null,null,null,null,false,true,true,null,0]
["Standard Class","Phillina Ober",263,null,null,null,null,null,false,false,true,null,0]
["First Class","Mick Brown",390,null,null,null,null,null,true,true,true,null,1]
["Second Class","Jack O'Briant",292,null,null,null,null,null,false,false,true,null,0]
["Standard Class","Lycoris Saunders",352,null,null,null,null,null,false,true,true,null,0]
["Standard Class","Maria Etezadi",143,null,null,null,null,null,false,false,true,null,0]
["First Class","Jack O'Briant",175,null,null,null,null,null,true,false,true,null,0]
["Second Class","Lycoris Saunders",170,null,null,null,null,null,false,false,true,null,0]
["Second Class","Maria Etezadi",381,null,null,null,null,null,false,true,true,null,0]
["First Class","Jack O'Briant",156,null,null,null,null,null,true,false,true,null,0]
["Standard Class","Lycoris Saunders",194,null,null,null,null,null,false,false,true,null,0]
["Standard Class","Maria Etezadi",121,null,null,null,null,null,false,false,true,null,0]

{"sheet":"offset","index":4,"n_rows":17,"n_cols":5,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,"what is the average run rate for the last 3 months of sales"]
[null,"Month","Sales"]
[null,"2021-01-01",345,null,{"$f":"AVERAGE(OFFSET(C2,\n\nCOUNT(C:C),\n\n0,\n\n-3))","v":110}]
[null,"2020-12-01",324]
[null,"2021-02-01",231]
[null,"2020-01-01",196]
[null,"2020-03-01",185]
[null,"2020-08-01",181]
[null,"2020-06-01",171]
[null,"2020-10-01",153]
[null,"2020-11-01",138]
[null,"2020-07-01",130]
[null,"2020-05-01",117]
[null,"2020-04-01",110]
[null,"2020-02-01",109]
[null,"2020-09-01",101]
[null,"2020-10-01",120]

{"sheet":"top n","index":5,"n_rows":22,"n_cols":7,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[]
["HIGHLIGHT ONLY TOP N VALUES"]
[]
[null,null,null,null,null,"CHOOSE TOP:",3]
[null,"PRODUCTS","SALES","RANK"]
[null,"Bookcases",1580,{"$f":"IF(_xlfn.RANK.AVG(C6,$C$6:$C$22)<=$G$4,\n\nC6,   \"\")","v":""}]
[null,"Chairs",7014,{"$f":"IF(_xlfn.RANK.AVG(C7,$C$6:$C$22)<=$G$4,\n\nC7,   \"\")","v":""}]
[null,"Furnishings",85704,""]
[null,"Tables",20360,""]
[null,"Appliances",97680,97680]
[null,"Art",11163,""]
[null,"Binders",93522,93522]
[null,"Envelopes",10339,""]
[null,"Fasteners",98553,98553]
[null,"Labels",67969,""]
[null,"Paper",80451,""]
[null,"Storage",40210,""]
[null,"Supplies",27123,""]
[null,"Accessories",87678,""]
[null,"Copiers",22008,""]
[null,"Machines",21613,""]
[null,"Phones",21034,""]

{"sheet":"form control","index":6,"n_rows":28,"n_cols":9,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
[]
["DYNAMIC CHART USING FORM CONTROLS"]
[]
[null,"DATE","SALES",null,"DATE","SALES",null,"ANCHOR",13]
[null,"2018-06-08",168,null,{"$f":"INDEX(INDIRECT(E$4), ROWS($E$5:$E5)+$I$4-1)","v":"2019-06-08"},{"$f":"INDEX(INDIRECT(F$4), ROWS($E$5:$E5)+$I$4-1)","v":200}]
[null,"2018-07-08",143,null,{"$f":"INDEX(INDIRECT(E$4), ROWS($E$5:$E6)+$I$4-1)","v":"2019-07-08"},{"$f":"INDEX(INDIRECT(F$4), ROWS($E$5:$E6)+$I$4-1)","v":143}]
[null,"2018-08-08",184,null,{"$f":"INDEX(INDIRECT(E$4), ROWS($E$5:$E7)+$I$4-1)","v":"2019-08-08"},{"$f":"INDEX(INDIRECT(F$4), ROWS($E$5:$E7)+$I$4-1)","v":140}]
[null,"2018-09-08",157,null,{"$f":"INDEX(INDIRECT(E$4), ROWS($E$5:$E8)+$I$4-1)","v":"2019-09-08"},{"$f":"INDEX(INDIRECT(F$4), ROWS($E$5:$E8)+$I$4-1)","v":113}]
[null,"2018-10-08",183,null,{"$f":"INDEX(INDIRECT(E$4), ROWS($E$5:$E9)+$I$4-1)","v":"2019-10-08"},{"$f":"INDEX(INDIRECT(F$4), ROWS($E$5:$E9)+$I$4-1)","v":151}]
[null,"2018-11-08",119,null,{"$f":"INDEX(INDIRECT(E$4), ROWS($E$5:$E10)+$I$4-1)","v":"2019-11-08"},{"$f":"INDEX(INDIRECT(F$4), ROWS($E$5:$E10)+$I$4-1)","v":170}]
[null,"2018-12-08",164]
[null,"2019-01-08",147]
[null,"2019-02-08",154]
[null,"2019-03-08",188]
[null,"2019-04-08",155]
[null,"2019-05-08",165]
[null,"2019-06-08",200]
[null,"2019-07-08",143]
[null,"2019-08-08",140]
[null,"2019-09-08",113]
[null,"2019-10-08",151]
[null,"2019-11-08",170]
[null,"2019-12-08",133,null,"https://turbofuture.com/computers/Creating-Dynamic-Charts-using-the-OFFSET-function-and-Named-Ranges-in-Excel-2007-and-Excel-2010"]
[null,"2020-01-08",166]
[null,"2020-02-08",120]
[null,"2020-03-08",129]
[null,"2020-04-08",114]
[null,"2020-05-08",171]

{"sheet":"vlookup","index":7,"n_rows":20,"n_cols":9,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"Customer Name","Q1","Q2","Q3","Q4"]
[null,"Darren Powers",194,114,183,155]
[null,"Phillina Ober",150,178,132,108]
[null,"Mick Brown",183,149,130,141]
[null,"Anthony Jacobs",138,155,165,162]
[null,"Seth Vernon",198,172,161,107]
[null,"Chris Selesnick",130,159,183,137]
[null,"Brian Dahlen",155,196,126,200]
[null,"Michael Moore",101,182,142,109]
[null,"David Wiener",107,174,198,180]
[null,"Toby Swindell",121,108,173,169]
[]
[]
[null,null,"Q1","Q2","Q3","Q4"]
[null,"Anthony Jacobs",{"$f":"VLOOKUP(B17, B:F, TRANSPOSE(ROW(INDIRECT(I17&\":\"&I18))), FALSE)","v":138},155,165,162,null,null,2]
[null,"Darren Powers",null,null,null,null,null,null,{"$f":"COUNTA(3:3)","v":5}]
[null,"Seth Vernon"]
[null,null,null,null,null,null,null,null,"deconstruction"]

{"sheet":"xlookup","index":8,"n_rows":13,"n_cols":12,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
[]
[]
[null,"Customer Name","Q1","Q2","Q3","Q4",null,null,"Q1","Q2","Q3","Q4"]
[null,"Darren Powers",194,114,183,155,null,"Anthony Jacobs",{"$f":"_xlfn.XLOOKUP(H4, B:B, C:F)","v":138},155,165,162]
[null,"Phillina Ober",150,178,132,108,null,"Darren Powers"]
[null,"Mick Brown",183,149,130,141,null,"Seth Vernon"]
[null,"Anthony Jacobs",138,155,165,162]
[null,"Seth Vernon",198,172,161,107]
[null,"Chris Selesnick",130,159,183,137]
[null,"Brian Dahlen",155,196,126,200]
[null,"Michael Moore",101,182,142,109]
[null,"David Wiener",107,174,198,180]
[null,"Toby Swindell",121,108,173,169]

{"sheet":"Sheet11","index":9,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Sample","index":10,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Product","Currency","Price (Currency)","Transaction Date","Base","Price in Base"]
["Frames","USD",5000,"2020-11-25","INR",369444.91169050004]
[]
[]
[]
[]
[]
[]
["https://www.youtube.com/watch?v=bNbu5ANUeAw&t=637s"]