{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Data List for BBQ","Pivot table and chart","Data List for BBQ (2)","Data List for BBQ (3)","Tax Rates","VBA example","Record Macro Example","Total Summary","Budget"],"not_rendered":{"charts":1,"images":0,"macros":1,"external_links":0,"defined_names":8,"comments":0,"pivot_tables":1},"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":"Data List for BBQ","index":0,"n_rows":28,"n_cols":8,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["BBQ "]
["Date of BBQ",null,"2012-07-04"]
["Expected number of people",null,15]
["Budget",null,300]
[]
["Description","Category","Quantity","Unit Price","Subtotal","Tax Rate","Total with Tax","Assigned"]
["cupcakes","snack",45,2.99,{"$f":"+C7*D7","v":134.55000000000001},{"$f":"+VLOOKUP(B7,TaxRates,3,0)","v":7.0000000000000007E-2},{"$f":"ROUND(E7*F7+E7,2)","v":143.97},"Greg"]
["pounds of chicken","food",16,3.99,63.84,0,63.84,"Brian"]
["pounds of beef","food",10,4.99,49.900000000000006,0,49.9,"Jonathan"]
["soda 6 packs","beverage",6,4.99,29.94,7.0000000000000007E-2,32.04,"Greg"]
["large watermelon","food",3,6.99,20.97,0,20.97,"Brian"]
["corn by ear","food",30,0.6,18,0,18,"Jonathan"]
["paper plates 50 ct","supplies",2,6.99,13.98,7.0000000000000007E-2,14.96,"Jonathan"]
["assorted utensils 100 ct","supplies",2,6.99,13.98,7.0000000000000007E-2,14.96,"Brian"]
["tortilla chips large bag","snack",2,5.99,11.98,7.0000000000000007E-2,12.82,"Jonathan"]
["ketchup 12 oz.","food",3,2.99,8.9700000000000006,0,8.9700000000000006,"Jonathan"]
["potato chips large bag","snack",2,3.99,7.98,7.0000000000000007E-2,8.5399999999999991,"Brian"]
["cups 50 ct","supplies",2,3.99,7.98,7.0000000000000007E-2,8.5399999999999991,"Jonathan"]
["hot dog 12 pack","food",2,3.99,7.98,0,7.98,"Greg"]
["buns 12 pack","food",4,1.99,7.96,0,7.96,"Brian"]
["pickles large jar","snack",1,6.99,6.99,7.0000000000000007E-2,7.48,"Jonathan"]
["napkins 200 ct","supplies",2,2.59,5.18,7.0000000000000007E-2,5.54,"Greg"]
["mustard 12 oz.","food",2,1.99,3.98,0,3.98,"Brian"]
[]
[null,null,null,null,null,"total",{"$f":"SUM(G7:G24)","v":430.4500000000001}]
[null,null,null,null,null,"Note:  CTL + SHIFT + Enter is needed to calculate following formulas with ARRAYS:"]
[null,null,null,null,null,"tally using nested sum and if ",{"$f":"SUM(IF(G7:G23>10,1,0))","v":9}]
[null,null,null,null,null,"adding using nested sum and if ",{"$f":"SUM(IF(G7:G23>0,G7:G23,0)*IF(B7:B23=\"food\",1,0))","v":181.6}]

{"sheet":"Pivot table and chart","index":1,"n_rows":20,"n_cols":5,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Sum of Total with Tax","Column Labels"]
["Row Labels","Brian","Greg","Jonathan","Grand Total"]
["cupcakes",null,79.98,null,79.98]
["pounds of chicken",63.84,null,null,63.84]
["pounds of beef",null,null,49.9,49.9]
["soda 6 packs",null,32.04,null,32.04]
["large watermelon",20.97,null,null,20.97]
["corn by ear",null,null,18,18]
["paper plates 50 ct",null,null,14.96,14.96]
["assorted utensils 100 ct",14.96,null,null,14.96]
["tortilla chips large bag",null,null,12.82,12.82]
["ketchup 12 oz.",null,null,8.9700000000000006,8.9700000000000006]
["cups 50 ct",null,null,8.5399999999999991,8.5399999999999991]
["potato chips large bag",8.5399999999999991,null,null,8.5399999999999991]
["hot dog 12 pack",null,7.98,null,7.98]
["buns 12 pack",7.96,null,null,7.96]
["pickles large jar",null,null,7.48,7.48]
["napkins 200 ct",null,5.54,null,5.54]
["mustard 12 oz.",3.98,null,null,3.98]
["Grand Total",120.25,125.54,120.66999999999999,366.46000000000004]

{"sheet":"Data List for BBQ (2)","index":2,"n_rows":28,"n_cols":8,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["BBQ "]
["Date of BBQ"]
["Expected number of people"]
["Budget"]
[]
["Description","Category","Quantity","Unit Price","Subtotal","Tax Rate","Total with Tax","Assigned"]
["cupcakes","snack",35,2.99,{"$f":"+C7*D7","v":104.65},{"$f":"+VLOOKUP(B7,TaxRates,3,0)","v":7.0000000000000007E-2},{"$f":"ROUND(E7*F7+E7,2)","v":111.98},"Greg"]
["pounds of chicken","food",16,3.99,63.84,0,63.84,"Brian"]
["pounds of beef","food",10,4.99,49.900000000000006,0,49.9,"Jonathan"]
["soda 6 packs","beverage",6,4.99,29.94,7.0000000000000007E-2,32.04,"Greg"]
["large watermelon","food",3,6.99,20.97,0,20.97,"Brian"]
["corn by ear","food",30,0.6,18,0,18,"Jonathan"]
["paper plates 50 ct","supplies",2,6.99,13.98,7.0000000000000007E-2,14.96,"Jonathan"]
["assorted utensils 100 ct","supplies",2,6.99,13.98,7.0000000000000007E-2,14.96,"Brian"]
["tortilla chips large bag","snack",2,5.99,11.98,7.0000000000000007E-2,12.82,"Jonathan"]
["ketchup 12 oz.","food",3,2.99,8.9700000000000006,0,8.9700000000000006,"Jonathan"]
["potato chips large bag","snack",2,3.99,7.98,7.0000000000000007E-2,8.5399999999999991,"Brian"]
["cups 50 ct","supplies",2,3.99,7.98,7.0000000000000007E-2,8.5399999999999991,"Jonathan"]
["hot dog 12 pack","food",2,3.99,7.98,0,7.98,"Greg"]
["buns 12 pack","food",4,1.99,7.96,0,7.96,"Brian"]
["pickles large jar","snack",1,6.99,6.99,7.0000000000000007E-2,7.48,"Jonathan"]
["napkins 200 ct","supplies",2,2.59,5.18,7.0000000000000007E-2,5.54,"Greg"]
["mustard 12 oz.","food",2,1.99,3.98,0,3.98,"Brian"]
[]
[null,null,null,null,null,"total",{"$f":"SUM(G7:G24)","v":398.46000000000009}]
[]
[null,null,null,null,null,null,{"$f":"SUM(IF(G7:G23>0,1,0))","v":17}]
[null,null,null,null,null,null,{"$f":"SUM(IF(G7:G23>0,G7:G23,0)*IF(B7:B23=\"food\",1,0))","v":181.6}]

{"sheet":"Data List for BBQ (3)","index":3,"n_rows":28,"n_cols":8,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["BBQ "]
["Date of BBQ"]
["Expected number of people"]
["Budget"]
[]
["Description","Category","Quantity","Unit Price","Subtotal","Tax Rate","Total with Tax","Assigned"]
["cupcakes","snack",25,2.99,{"$f":"+C7*D7","v":74.75},{"$f":"+VLOOKUP(B7,TaxRates,3,0)","v":7.0000000000000007E-2},{"$f":"ROUND(E7*F7+E7,2)","v":79.98},"Greg"]
["pounds of chicken","food",16,3.99,63.84,0,63.84,"Brian"]
["pounds of beef","food",10,4.99,49.900000000000006,0,49.9,"Jonathan"]
["soda 6 packs","beverage",6,4.99,29.94,7.0000000000000007E-2,32.04,"Greg"]
["large watermelon","food",3,6.99,20.97,0,20.97,"Brian"]
["corn by ear","food",30,0.6,18,0,18,"Jonathan"]
["paper plates 50 ct","supplies",2,6.99,13.98,7.0000000000000007E-2,14.96,"Jonathan"]
["assorted utensils 100 ct","supplies",2,6.99,13.98,7.0000000000000007E-2,14.96,"Brian"]
["tortilla chips large bag","snack",2,5.99,11.98,7.0000000000000007E-2,12.82,"Jonathan"]
["ketchup 12 oz.","food",3,2.99,8.9700000000000006,0,8.9700000000000006,"Jonathan"]
["potato chips large bag","snack",2,3.99,7.98,7.0000000000000007E-2,8.5399999999999991,"Brian"]
["cups 50 ct","supplies",2,3.99,7.98,7.0000000000000007E-2,8.5399999999999991,"Jonathan"]
["hot dog 12 pack","food",2,3.99,7.98,0,7.98,"Greg"]
["buns 12 pack","food",4,1.99,7.96,0,7.96,"Brian"]
["pickles large jar","snack",1,6.99,6.99,7.0000000000000007E-2,7.48,"Jonathan"]
["napkins 200 ct","supplies",2,2.59,5.18,7.0000000000000007E-2,5.54,"Greg"]
["mustard 12 oz.","food",2,1.99,3.98,0,3.98,"Brian"]
[]
[null,null,null,null,null,"total",{"$f":"SUM(G7:G24)","v":366.46000000000009}]
[]
[null,null,null,null,null,null,{"$f":"SUM(IF(G7:G23>0,1,0))","v":17}]
[null,null,null,null,null,null,{"$f":"SUM(IF(G7:G23>0,G7:G23,0)*IF(B7:B23=\"food\",1,0))","v":181.6}]

{"sheet":"Tax Rates","index":4,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
["Category","NY rate","NJ rate"]
["beverage",0.09,7.0000000000000007E-2]
["food",0,0]
["snack",0.09,7.0000000000000007E-2]
["supplies",0.09,7.0000000000000007E-2]

{"sheet":"VBA example","index":5,"n_rows":60,"n_cols":2,"rows_kept":60,"cut":false,"hidden":false,"merged":[]}
["Create our own excel function \"Sum of Square Numbers\""]
[]
["End Value","Total"]
[1,{"$f":"SumofSquares(A4)","v":1}]
[2,{"$f":"SumofSquares(A5)","v":5}]
[3,14]
[]
[100,{"$f":"SumofSquares(A8)*IF(SumofSquares(A8)<1000000,1,0)","v":338350}]
[101,348551]
[102,358955]
[103,369564]
[104,380380]
[105,391405]
[106,402641]
[107,414090]
[108,425754]
[109,437635]
[110,449735]
[111,462056]
[112,474600]
[113,487369]
[114,500365]
[115,513590]
[116,527046]
[117,540735]
[118,554659]
[119,568820]
[120,583220]
[121,597861]
[122,612745]
[123,627874]
[124,643250]
[125,658875]
[126,674751]
[127,690880]
[128,707264]
[129,723905]
[130,740805]
[131,757966]
[132,{"$f":"SumofSquares(A40)*IF(SumofSquares(A40)<1000000,1,0)","v":775390}]
[133,793079]
[134,811035]
[135,829260]
[136,847756]
[137,866525]
[138,885569]
[139,904890]
[140,924490]
[141,944371]
[142,964535]
[143,984984]
[144,0]
[145,0]
[146,0]
[147,0]
[148,0]
[149,0]
[150,0]
[151,0]
[152,0]

{"sheet":"Record Macro Example","index":6,"n_rows":14,"n_cols":12,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
[]
["Saturday"]
["Monday"]
["Tuesday",null,null,null,null,null,null,null,null,null,null,"Saturday"]
["Wednesday",null,null,null,null,"Saturday","Sunday",null,null,null,null,"Monday"]
["Thursday",null,null,null,null,"Monday","Monday",null,null,null,null,"Tuesday"]
["Friday",null,null,null,null,"Tuesday","Tuesday",null,"Saturday",null,null,"Wednesday"]
["Saturday ",null,null,null,null,"Wednesday","Wednesday",null,"Monday",null,null,"Thursday"]
["Sunday",null,null,null,null,"Thursday","Thursday",null,"Tuesday",null,null,"Friday"]
[null,null,null,null,null,"Friday","Friday",null,"Wednesday",null,null,"Saturday "]
[null,null,null,null,null,"Saturday ","Saturday ",null,"Thursday",null,null,"Sunday"]
[null,null,null,null,null,"Sunday",null,null,"Friday"]
[null,null,null,null,null,null,null,null,"Saturday "]
[null,null,null,null,null,null,null,null,"Sunday"]

{"sheet":"Total Summary","index":7,"n_rows":4,"n_cols":2,"rows_kept":4,"cut":false,"hidden":false,"merged":[]}
["Year","Total"]
[2012,{"$f":"BBQTotal","v":430.4500000000001}]
[2011,{"$f":"BBQTotal2011","v":398.46000000000009}]
[2010,{"$f":"BBQTotal2010","v":366.46000000000009}]

{"sheet":"Budget","index":8,"n_rows":7,"n_cols":4,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[]
["2013-07-01",{"$f":"'Total Summary'!B2","v":430.4500000000001},{"$f":"BBQTotal*1.1","v":473.49500000000018}]
[]
[null,null,{"$f":"HistoricalSummary","v":"Year"},"Total"]
[null,null,2012,430.4500000000001]
[null,null,2011,398.46000000000009]
[null,null,2010,366.46000000000009]