{"text_format":"1","workbook":"xlsx","n_sheets":43,"sheets":["Topics","References","C AR","Assumption Sheet","Oak","Sea","Tac","TFN on Diff Sheet","Cash","AR","Homework==>>","HW(1)","HW(1an)","HW(2)","HW(2an)","HW(3)","HW(3an)","HW(4)","HW(4an)","HW(5)","HW(5an)","HW(6)","AssumptionsFor6","HW(6an)","HW(7)","HW(7an)","HW(8)","HW(8an)","HW(9)","Sioux","Tyrone","Chin","Phil","Tommy","HW(9an)","HW(10)","HW(10an)","HW(11)","HW(11an)","HW(12)","HW(12an)","HW(13)","HW(13an)"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":10,"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":"Topics","index":0,"n_rows":17,"n_cols":2,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["Highline Excel 2016 Class 04: References: Relative, Absolute, Mixed, Sheet, Workbook, 3-D, Table (24 Examples)"]
[]
["Topics:"]
[1,"Relative Cell Reference"]
[2,"Absolute Cell Reference"]
[3,"Mixed Cell Reference with row locked cell reference (also known as row absolute, column relative)"]
[4,"Mixed Cell Reference with column locked cell reference (also known as column absolute, row relative)"]
[5,"Sheet Cell Reference"]
[6,"Workbook Cell Reference"]
[7,"3-D cell reference"]
[8,"Defined Name"]
[9,"Table Formula Nomenclature (Structured References)"]
[]
["Other Topics:"]
[10,"Finance and Cash Flow"]
[11,"Ratios and what they mean"]
[12,"Expandable Ranges and ROWS function for \"Incrementing Numbers\""]

{"sheet":"References","index":1,"n_rows":337,"n_cols":14,"rows_kept":337,"cut":false,"hidden":false,"merged":[[87,1,87,3],[87,4,87,6],[123,7,124,7],[123,0,124,0],[123,1,124,1],[123,2,123,4],[123,5,124,5],[123,6,124,6]]}
["When we copy a formula that contains cell references, we need to consider whether we need:"]
["Relative, Absolute, Mixed with the Column Locked or Mixed with the Row Locked."]
[]
["Example 1: Not Copying Formula.",null,null,null,null,null,null,null,null,null,null,null,null,"In Finance, Cash Flow is always the direction of the cash: In or Out of your wallet or purse:"]
["Goal: Calculate Net Income.",null,null,null,null,null,null,null,null,null,null,null,null,"Cash Flow Negative = Cash OUT of your wallet or purse."]
["Month","Jan",null,null,null,null,null,null,null,null,null,null,null,"Examples:"]
["Revenue",65487,null,null,null,null,null,null,null,null,null,null,null,"When you put money into a bank or an investment."]
["Expenses",52149,null,null,null,null,null,null,null,null,null,null,null,"When you pay your mortgage."]
["Net Income",{"$f":"B7-B8","v":13338},null,null,null,null,null,null,null,null,null,null,null,"When you pay your rent."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Cash Flow Positive = Cash IN to your wallet or purse."]
["Example 2: Not Copying Formula.",null,null,null,null,"**In Finance, Cash Flow is always the direction of the cash: In or Out of your wallet or purse)",null,null,null,null,null,null,null,"Examples:"]
["Goal: Estimate the Future Value of an Investment.",null,null,null,null,null,null,null,null,null,null,null,null,"When the bank receives cash."]
["Amount",3500,null,null,null,"Amount deposited each year (negative because out of your wallet/purse)",null,null,null,null,null,null,null,"When you receive a paycheck."]
["Years",15,null,null,null,"Number of years you make deposits",null,null,null,null,null,null,null,"When you receive a retirement payment."]
["Annual Rate",4.4999999999999998E-2,null,"Check:",null,"Annual interest rate investment is expected to earn"]
["Future Value",{"$f":"FV(B15,B14,-B13)","v":72744.190018200708},null,{"$f":"B13*((1+B15)^B14-1)/B15","v":72744.190018200708},null,{"$f":"\"Future Value = Estimate for how much it is worth in \"&B14&\" \"&A14","v":"Future Value = Estimate for how much it is worth in 15 Years"},null,null,null,null,null,null,null,"FV arguments:"]
["Total Invested",{"$f":"B13*B14","v":52500},null,null,null,null,null,null,null,null,null,null,null,"rate = Period Rate (Year, Month, Day…). Same rate each period. If Rate is annual and you need monthly use Annual/12"]
["Total Interest",{"$f":"B16-B17","v":20244.190018200708},null,null,null,null,null,null,null,null,null,null,null,"nper = Total Number of Periods (Year, Month, Day…). Each period is exactly the same (equal time intervals). If time is given in years and you are making deposits monthly use Annual*12"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"pmt = Equal payments each period. Amounts cannot change."]
[]
["Example 3: Relative Cell Reference."]
["Goal: Calculate Net Income for each Month."]
["Month","Jan","Feb","Mar","Apr","May"]
["Revenue",{"$f":"B61","v":65487},{"$f":"C61","v":75894},{"$f":"D61","v":65007},{"$f":"E61","v":95449},{"$f":"F61","v":99220}]
["Expenses",{"$f":"B61*SUM($B$72:$B$75)","v":55932.972012393933},{"$f":"C61*SUM($B$72:$B$75)","v":64821.674193483057},55523.000162012191,81523.756556430875,84744.597905992428]
["Net Income",{"$f":"B24-B25","v":9554.0279876060667},{"$f":"C24-C25","v":11072.325806516943},9483.9998379878089,13925.243443569125,{"$f":"F24-F25","v":14475.402094007572}]
[]
[]
["Example 4: Relative & Absolute Cell Reference."]
["Goal: Estimate the Future Value of an Investment at different rates."]
["Amount",3500]
["Years",15]
[]
["Annual Rate","Future Value",null,null,null,null,null,null,null,"Check:"]
[0.03,{"$f":"FV(A35,$B$32,-$B$31)","v":65096.198603422519},null,null,null,null,null,null,null,{"$f":"$B$31*((1+A35)^$B$32-1)/A35","v":65096.198603422519}]
[0.04,{"$f":"FV(A36,$B$32,-$B$31)","v":70082.55673185522},null,null,null,null,null,null,null,70082.556731855206]
[0.05,75524.972558795751,null,null,null,null,null,null,null,75524.972558795751]
[0.06,81465.894597482067,null,null,null,null,null,null,null,81465.894597482067]
[7.0000000000000007E-2,87951.577035766721,null,null,null,null,null,null,null,87951.577035766706]
[0.08,{"$f":"FV(A40,$B$32,-$B$31)","v":95032.398746174367},null,null,null,null,null,null,null,95032.398746174367]
[]
["Example 5: Relative & Absolute Cell Reference."]
["Goal: Calculate each Expense as a % of Revenue (Expense Ratios) to use in next year's Budget."]
[]
["Ave. of Last 5 Year's Income Statement:",null,null,null,"* When we do division or calculate a ratio, if you keep the units in both the numerator and denominator, and,"]
[null,null,null,null," keep a 1 in the denominator, the meaning of the ratio is revealed."]
["Category","Amounts","% of Revenue",null,"* Each % tells us \"how many pennies for each dollar that comes into cash register, goes to each category.\""]
["Revenue",98758,{"$f":"B48/$B$48","v":1},null,"* Once we calculate these % we can use them to make estimates for future periods (budgeting)."]
["Expenses:",null,null,null,"Meaning of each Ratio:"]
["COGS",45879,{"$f":"B50/$B$48","v":0.46455983312744287},null,{"$f":"IF(ISNUMBER(C50),DOLLAR(B50,0)&\" \"&A50&\"/\"&DOLLAR($B$48,0)&\" \"&$A$48&\"  =  \"&DOLLAR(C50)&\" \"&A50&\"/\"&DOLLAR($C$48)&\" \"&$A$48&\"  =  \"&DOLLAR(C50)&\" of \"&A50&\" for every \"&DOLLAR($C$48)&\" of \"&$A$48,\"\")","v":"$45,879 COGS/$98,758 Revenue  =  $0.46 COGS/$1.00 Revenue  =  $0.46 of COGS for every $1.00 of Revenue"}]
["Operating",19237,0.19478928289353775,null,"$19,237 Operating/$98,758 Revenue  =  $0.19 Operating/$1.00 Revenue  =  $0.19 of Operating for every $1.00 of Revenue"]
["Administrative",15247,0.15438749265882257,null,"$15,247 Administrative/$98,758 Revenue  =  $0.15 Administrative/$1.00 Revenue  =  $0.15 of Administrative for every $1.00 of Revenue"]
["Other",3987,4.0371412948824396E-2,null,"$3,987 Other/$98,758 Revenue  =  $0.04 Other/$1.00 Revenue  =  $0.04 of Other for every $1.00 of Revenue"]
["Total Expenses",{"$f":"SUM(B50:B53)","v":84350},0.85410802162862753,null,"$84,350 Total Expenses/$98,758 Revenue  =  $0.85 Total Expenses/$1.00 Revenue  =  $0.85 of Total Expenses for every $1.00 of Revenue"]
["Net Income",{"$f":"B48-B54","v":14408},0.14589197837137244,null,"$14,408 Net Income/$98,758 Revenue  =  $0.15 Net Income/$1.00 Revenue  =  $0.15 of Net Income for every $1.00 of Revenue"]
[]
[]
["Example 6: Mixed Cell References & Assumption Tables."]
["Goal: Calculate Expenses for Budgeting Process."]
["Month","Jan","Feb","Mar","Apr","May"]
[{"$f":"A48","v":"Revenue"},65487,75894,65007,95449,99220]
[{"$f":"A50","v":"COGS"},{"$f":"ROUND(B$61*$B72,2)","v":30422.63},35257.300000000003,30199.64,44341.77,46093.63]
["Operating",12756.17,14783.34,12662.67,18592.439999999999,19326.990000000002]
["Administrative",10110.370000000001,11717.08,10036.27,14736.13,15318.33]
["Other",2643.8,3063.95,2624.42,3853.41,4005.65]
["Total Expenses",{"$f":"SUM(B62:B65)","v":55932.970000000008},{"$f":"SUM(C62:C65)","v":64821.67},{"$f":"SUM(D62:D65)","v":55523},{"$f":"SUM(E62:E65)","v":81523.75},{"$f":"SUM(F62:F65)","v":84744.599999999991}]
["Net Income",{"$f":"B61-B66","v":9554.0299999999916},{"$f":"C61-C66","v":11072.330000000002},{"$f":"D61-D66","v":9484},{"$f":"E61-E66","v":13925.25},{"$f":"F61-F66","v":14475.400000000009}]
[]
["This Assumption Table can be used with Mixed Cell References because"]
["expenses in Assumption Table are oriented in the same direction as the formula table."]
["Assumption Table (Formula Inputs):"]
[{"$f":"A50","v":"COGS"},{"$f":"B50/$B$48","v":0.46455983312744287},null,"<<= Expenses are Vertical in Assumption Table & Vertical in Formula Table"]
[{"$f":"A51","v":"Operating"},{"$f":"B51/$B$48","v":0.19478928289353775},null,"Horizontal / Horizontal works."]
[{"$f":"A52","v":"Administrative"},{"$f":"B52/$B$48","v":0.15438749265882257},null,"Vertical / Vertical works."]
[{"$f":"A53","v":"Other"},{"$f":"B53/$B$48","v":4.0371412948824396E-2},null,"Horizontal / Vertical will NOT work."]
[]
["This Assumption Table can NOT be used with Mixed Cell References"]
["because expenses in Assumption Table are NOT oriented in the same direction as the formula table."]
["Assumption Table (Formula Inputs):"]
[{"$f":"TRANSPOSE(A72:B75)","v":"COGS"},"Operating","Administrative","Other"]
[0.46455983312744287,0.19478928289353775,0.15438749265882257,4.0371412948824396E-2]
[]
["Example 7: Relative & Mixed Cell Reference."]
["Goal: Create \"Common Sized Income Statement\" for Accounting Report."]
[]
["**Called \"Common Sized Income Statement\" because when you calculate % you can compare to other % that represent numbers with different magnitudes."]
[]
[null,"4th Quarter Income Statement",null,null,"4th Quarter Common Sized Income St."]
[null,"Oct","Nov","Dec","Oct","Nov","Dec"]
["Revenue",100983.22,165612.47,233513.59,{"$f":"B90/B$90","v":1},1,1]
["Expenses:",null,null,null,0,0,0]
["COGS",44484.77,74875.009999999995,86803.77,0.44051645411980322,0.45210973545651478,0.37172898588043635]
["Operating",19782.41,28288.84,43564.82,0.19589799176536457,0.17081346591835747,0.18656224676259742]
["Administrative",17438.66,26158,39237,0.17268869025962927,0.15794704348048189,0.16802876440724498]
["Other",4024.15,6462.94,8143.31,3.9849689879170022E-2,3.9024476840421492E-2,3.4872959642306044E-2]
["Total Expenses",{"$f":"SUM(B92:B95)","v":85729.989999999991},{"$f":"SUM(C92:C95)","v":135784.78999999998},177748.9,0.84895282602396704,0.8198947216957756,0.7611929566925848]
["Net Income",{"$f":"B90-B96","v":15253.23000000001},{"$f":"C90-C96","v":29827.680000000022},55764.69,0.15104717397603296,0.18010527830422446,0.23880704330741523]
[]
[]
["Example 8: Mixed Cell References & Absolute Cell References.."]
["Goal: Estimate the Future Value of an Investment at different rates & length of investment."]
["Amount",3500]
["Start Rate",0.02]
["Increment Rate",0.01]
["Start Year",10]
["Increment Year",5]
[]
["Future Value ($):"]
["Annual Rate/Years",{"$f":"B105","v":10},{"$f":"B109+$B$106","v":15},20,25,30,35,40,45,50]
[{"$f":"B103","v":0.02},{"$f":"FV($A110,B$109,-$B$102)","v":38324.02349908249},60526.959206722611,85040.794296211985,112106.04903132767,141988.27721808685,174980.67171592958,211406.94113259905,251624.48592773508,296027.90508788079]
[{"$f":"A110+$B$104","v":0.03},40123.577590147543,65096.198603422519,94046.31071143155,127607.42512632495,166513.9549721269,211617.28634334428,263904.40906655841,324519.51485932304,394789.03551582672]
[0.04,42021.374930355152,70082.55673185522,104223.27501542435,145760.67900514955,196297.28212740994,257782.78699354504,332589.30494445772,423602.87212787318,534334.79279935325]
[0.05,44022.623874420904,75524.972558795751,115730.83936010944,167044.84586295701,232535.96626054638,316121.07573145756,422799.20984872541,558950.54554449685,732717.98500275798]
[6.0000000000000005E-2,46132.782298333179,81465.894597482053,128749.56921241611,192025.79198503678,276703.65175327344,390021.72955155373,541666.87966567907,744602.29826739337,1016175.6660411701]
[7.0000000000000007E-2,48357.567864478275,87951.577035766721,143484.22312430898,221371.63200614453,330612.75213310146,483829.07423077163,698722.89196034765,1000122.5879336947,1422851.2531535665]
[0.08,50702.968630684467,95032.398746174367,160166.87504340717,255870.78983459549,396491.23889696324,603108.81287652452,906697.81548499514,1352769.6608253291,2008195.5474538698]
[0.09,53175.254011915735,102763.20676562589,179060.41874693378,296453.13679345755,477076.38491066045,754987.64127563534,1182588.5576551405,1840505.5707338399,2852792.4473929829]
[9.9999999999999992E-2,55780.986103500065,111203.68592954795,200462.49822639633,344214.70801859372,575729.07941102551,948585.28968225117,1549073.9448861626,2516166.928978615,4073679.85078935]
[]
[]
["Example 9: Mixed Cell References & Relative Cell References."]
["Goal: Calculate Profit for each Markup Price Schedule"]
["Mortimer's Furniture Price Schedule"]
["Items","Wholesale\nCost","Markup on Sell Price",null,null,{"$f":"\"Profit at \"&TEXT(C125,\"0%\")","v":"Profit at 50%"},{"$f":"\"Profit at \"&TEXT(D125,\"0%\")","v":"Profit at 55%"},{"$f":"\"Profit at \"&TEXT(E125,\"0%\")","v":"Profit at 60%"}]
[null,null,{"$f":"B141","v":0.5},{"$f":"C125+$B142","v":0.55000000000000004},{"$f":"D125+$B142","v":0.60000000000000009}]
[{"$f":"A135","v":"Sofa"},275,{"$f":"ROUND($B126/(1-C$125),2)","v":550},611.11,687.5,{"$f":"C126-$B126","v":275},{"$f":"D126-$B126","v":336.11},412.5]
["Lamp",125,250,277.77999999999997,312.5,{"$f":"C127-$B127","v":125},{"$f":"D127-$B127","v":152.77999999999997},{"$f":"E127-$B127","v":187.5}]
["End Table",225,450,500,562.5,225,275,337.5]
["Chair",215,430,477.78,537.5,215,262.77999999999997,322.5]
["Rug",425,850,944.44,1062.5,425,519.44000000000005,637.5]
["Picture",100,200,222.22,250,100,122.22,150]
["Totals",{"$f":"SUM(B126:B131)","v":1365},2730,3033.3299999999995,3412.5,1365,1668.3300000000002,2047.5]
[]
["Assumption Table (Formula Inputs)"]
["Sofa",275]
["Lamp",125]
["End Table",225]
["Chair",215]
["Rug",425]
["Picture",100]
["Markup on Sell Price",0.5]
["Increment",0.05]
[]
[]
[]
["Example 10: Mixed Cell References & Relative Cell References in Expandable Range."]
["Goal: Calculate Selling Price and Profit Margin based on different "]
[]
["No.","List Top 5:",null,"Points"]
[{"$f":"ROWS(A$150:A150)","v":1},{"$f":"LARGE($D$150:$D$158,ROWS(B$150:B150))","v":99},null,87]
[{"$f":"ROWS(A$150:A151)","v":2},{"$f":"LARGE($D$150:$D$158,ROWS(B$150:B151))","v":95},null,95]
[{"$f":"ROWS(A$150:A152)","v":3},{"$f":"LARGE($D$150:$D$158,ROWS(B$150:B152))","v":87},null,85]
[{"$f":"ROWS(A$150:A153)","v":4},{"$f":"LARGE($D$150:$D$158,ROWS(B$150:B153))","v":87},null,62]
[{"$f":"ROWS(A$150:A154)","v":5},{"$f":"LARGE($D$150:$D$158,ROWS(B$150:B154))","v":85},null,99]
[null,null,null,79]
["ROWS Function is used to \"Increment Numbers\"",null,null,87]
["or create Sequential Numbers like: 1, 2, 3, 4, 5",null,null,67]
[null,null,null,58]
[]
[]
["Example 11: Saving Formula Inputs with Scenario feature."]
["Goal: Create different sets of assumptions for budget."]
["Budget"]
["Account","Jan","Feb","Mar","Apr","May","Jun","Total"]
["Revenue",1000,1250,1370,1250,2000,2215,{"$f":"SUM(B165:G165)","v":9085}]
[{"$f":"A176","v":"COGS"},{"$f":"ROUND(B$165*$B176,2)","v":290},362.5,397.3,362.5,580,642.35,2634.65]
["Office Expense",70,87.5,95.9,87.5,140,155.05000000000001,635.95000000000005]
["Admin Expense",110,137.5,150.69999999999999,137.5,220,243.65,999.35]
["Wage Expense",250,312.5,342.5,312.5,500,553.75,2271.25]
["Other Expense",40,50,54.8,50,80,88.6,363.4]
["Depreciation",120,150,164.4,150,240,265.8,1090.2]
["Total Expenses",{"$f":"SUM(B166:B171)","v":880},1100,1205.6000000000001,1100,1760,1949.2,7994.8]
["Net Income",{"$f":"B165-B172","v":120},150,164.39999999999986,150,240,265.79999999999995,1090.1999999999998]
[]
["Assumptions - Formula Inputs"]
["COGS",0.28999999999999998]
["Office Expense",7.0000000000000007E-2,null,"Scenario keyboard: Alt, T, E    (old Alt keyboard from Excel 2003 and earlier)"]
["Admin Expense",0.11]
["Wage Expense",0.25]
["Other Expense",0.04]
["Depreciation",0.12]
[]
[]
["Example 12: Sheet Reference."]
["Goal: Check if Schedule Of Accounts Receivable matches the AR Controlling Account."]
["Schedule Of Accounts Receivable"]
["Name","Amount"]
["Name1",50]
["Name2",601]
["Name3",845]
["Name4",32]
["Name5",155]
["Name6",45]
["Name7",87,null,"Sheet Reference:"]
["Name8",22,null,"1) Type equal sign"]
["Total AR",{"$f":"SUM(B188:B195)","v":1837},null,"2) Click on sheet"]
[null,null,null,"3) click on cell on \"clicked on sheet\""]
["Controlling Account Balance",{"$f":"'C AR'!A5","v":1836},null,"Don't click back on sheet with formula!!!"]
["Check to See If In Balance",{"$f":"B198=B196","v":false},null,"4) Hit Enter!!"]
[]
[]
["Example 13: Sheet Reference."]
["Goal: Calculate Jan & Feb Commissions, when Commission Rates are on an Assumption Sheet."]
["Jan Sales","Feb Sales","Jan Comm $","Feb Comm $"]
[28463.4,14231.7,{"$f":"ROUND(A205*'Assumption Sheet'!A$2,2)","v":782.74},{"$f":"ROUND(B205*'Assumption Sheet'!B$2,2)","v":466.09}]
[33873.699999999997,45008.47,{"$f":"ROUND(A206*'Assumption Sheet'!A$2,2)","v":931.53},{"$f":"ROUND(B206*'Assumption Sheet'!B$2,2)","v":1474.03}]
[6050.6,19361.920000000002,{"$f":"ROUND(A207*'Assumption Sheet'!A$2,2)","v":166.39},{"$f":"ROUND(B207*'Assumption Sheet'!B$2,2)","v":634.1}]
[24665.4,24397.72,{"$f":"ROUND(A208*'Assumption Sheet'!A$2,2)","v":678.3},{"$f":"ROUND(B208*'Assumption Sheet'!B$2,2)","v":799.03}]
[17142.400000000001,19712.96,{"$f":"ROUND(A209*'Assumption Sheet'!A$2,2)","v":471.42},{"$f":"ROUND(B209*'Assumption Sheet'!B$2,2)","v":645.6}]
[10748.8,16123.199999999999,{"$f":"ROUND(A210*'Assumption Sheet'!A$2,2)","v":295.58999999999997},{"$f":"ROUND(B210*'Assumption Sheet'!B$2,2)","v":528.03}]
[2653.7,3715.1799999999994,{"$f":"ROUND(A211*'Assumption Sheet'!A$2,2)","v":72.98},{"$f":"ROUND(B211*'Assumption Sheet'!B$2,2)","v":121.67}]
[13052.6,22463.06,{"$f":"ROUND(A212*'Assumption Sheet'!A$2,2)","v":358.95},{"$f":"ROUND(B212*'Assumption Sheet'!B$2,2)","v":735.67}]
["Total",null,{"$f":"SUM(C205:C212)","v":3757.9},{"$f":"SUM(D205:D212)","v":5404.22}]
[]
[]
["Example 14: Workbook Reference."]
["Goal: Access Net Income Number from External Workbook."]
[]
["June Revenue and Expenses",null,null,null,null,null,null,null,null,null,"Workbook Reference:"]
["Rev",10000,null,null,null,null,null,null,null,null,"1) Type equal sign"]
["E1",500,null,null,null,null,null,null,null,null,"2) Click on Workbook"]
["E2",654,null,null,null,null,null,null,null,null,"3) Click on sheet"]
["E3",85,null,null,null,null,null,null,null,null,"4) click on cell on \"clicked on sheet\""]
["E4",958,null,null,null,null,null,null,null,null,"5) Hit Enter!!"]
["E5",6522,null,null,null,null,null,null,null,null,"Absolute Cell References are default, but you can change them."]
["E6",12,null,null,null,null,null,null,null,null,"You can update, change, open source or break link: Data Ribbon Tab, Connections group, Edit Links"]
[]
["June Net Income",{"$f":"B220-SUM(B221:B226)","v":1269}]
["May Net Income"," ='E:\\[May Net Income.xlsx]May NI'!$B$10"]
["Change"," =B228-B229"]
[]
[]
["Example 15: 3-D References."]
["Goal: Add numbers from across sheets that each contain an identical template for units."]
[]
["Corp Totals",null,null,null,null,null,null,"3-D Reference"]
[null,"Jan","Feb","Mar","Apr","Total",null,"1) click on first sheet"]
["Quad",{"$f":"SUM(Oak:Tac!B3)","v":31},{"$f":"SUM(Oak:Tac!C3)","v":25},{"$f":"SUM(Oak:Tac!D3)","v":14},{"$f":"SUM(Oak:Tac!E3)","v":17},{"$f":"SUM(B238:E238)","v":87},null,"2) Click on cell or range"]
["Sunset",{"$f":"SUM(Oak:Tac!B4)","v":12},{"$f":"SUM(Oak:Tac!C4)","v":29},{"$f":"SUM(Oak:Tac!D4)","v":21},{"$f":"SUM(Oak:Tac!E4)","v":27},89,null,"3) HOLD SHIFT"]
["Carlota",{"$f":"SUM(Oak:Tac!B5)","v":33},{"$f":"SUM(Oak:Tac!C5)","v":15},{"$f":"SUM(Oak:Tac!D5)","v":17},{"$f":"SUM(Oak:Tac!E5)","v":20},85,null,"4) Click on last sheet"]
["Aspen",{"$f":"SUM(Oak:Tac!B6)","v":18},{"$f":"SUM(Oak:Tac!C6)","v":33},{"$f":"SUM(Oak:Tac!D6)","v":10},{"$f":"SUM(Oak:Tac!E6)","v":7},68,null,"5) Hit Enter"]
["Yanaki",{"$f":"SUM(Oak:Tac!B7)","v":18},{"$f":"SUM(Oak:Tac!C7)","v":22},{"$f":"SUM(Oak:Tac!D7)","v":27},{"$f":"SUM(Oak:Tac!E7)","v":12},79]
["Tri-Fly",{"$f":"SUM(Oak:Tac!B8)","v":24},{"$f":"SUM(Oak:Tac!C8)","v":8},{"$f":"SUM(Oak:Tac!D8)","v":6},{"$f":"SUM(Oak:Tac!E8)","v":28},66]
["Total",{"$f":"SUM(B238:B243)","v":136},{"$f":"SUM(C238:C243)","v":132},{"$f":"SUM(D238:D243)","v":95},{"$f":"SUM(E238:E243)","v":111},474]
[]
[null,"Jan","Feb","Mar","Apr"]
[null,{"$f":"SUM(Oak:Tac!B3:B8)","v":136},{"$f":"SUM(Oak:Tac!C3:C8)","v":132},{"$f":"SUM(Oak:Tac!D3:D8)","v":95},{"$f":"SUM(Oak:Tac!E3:E8)","v":111}]
[]
[]
["Example 16: Defined Names.",null,null,null,null,null,null,null,null,"Create Defined Name:"]
["Goal: Estimate the Future Value of an Investment. Name Formula Inputs so that reading formulas is easier.",null,null,null,null,null,null,null,null,"Highlight range"]
["Amount",3500,null,null,null,null,null,null,null,"Click in Name Box (left of formula bar)"]
["Years",15,null,null,null,null,null,null,null,"Type Name"]
["Annual Rate",4.4999999999999998E-2,null,null,null,null,null,null,null,"Hit Enter"]
["Future Value",{"$f":"FV(Annual_Rate,Years,-Amount)","v":72744.190018200708}]
["Total Invested",{"$f":"Years*Amount","v":52500},null,null,null,null,null,null,null,"Defined Name Requirements:"]
["Total Interest",{"$f":"B255-B256","v":20244.190018200708},null,null,null,null,null,null,null,"The first character of a name must be: letter, underscore (_), or backslash (\\)."]
[null,null,null,null,null,null,null,null,null,"Characters allowed: letters, numbers, periods, and underscore characters."]
[null,null,null,null,null,null,null,null,null,"Max Length = 255 characters."]
["Example 17: Defined Names.",null,null,null,null,null,null,null,null,"No Spaces."]
["Goal: Name Fields so that it is fast, easy and accurate to enter ranges into formulas.",null,null,null,null,null,null,null,null,"Can't be same as cell reference, like: Z$100 or R1C1 or ROI2016."]
["Sales","Product",null,null,null,null,null,null,null,"Excel does not distinguish between uppercase and lowercase characters in names."]
[1140,"Quad",null,"Total Sales",{"$f":"SUM(Sales)","v":14570}]
[2501,"Sunset",null,"Max Sales",{"$f":"MAX(Sales)","v":2501}]
[1412,"Yanaki",null,"Min Sales",{"$f":"MIN(Sales)","v":1140},null,null,null,null,"Name Manager:"]
[1168,"Quad",null,null,null,null,null,null,null,"Where you go to see, edit or delete Defined Names or Table Names."]
[1329,"Quad",null,"Product","Sales"]
[1298,"Yanaki",null,"Quad",{"$f":"SUMIFS(Sales,Product,D268)","v":3637},null,null,null,null,"Keyboards:"]
[2394,"Sunset",null,"Sunset",{"$f":"SUMIFS(Sales,Product,D269)","v":6186},null,null,null,null,"Ctrl + Shift + F3 = Create Names From Selection"]
[1291,"Sunset",null,"Yanaki",{"$f":"SUMIFS(Sales,Product,D270)","v":4747},null,null,null,null,"Ctrl + F3 = open Name Manager"]
[2037,"Yanaki",null,null,null,null,null,null,null,"F3 = Paste Name or List of Names"]
[]
[null,null,null,null,null,null,null,null,null,"Apply Names: Formulas Ribbon Tab, Defined Name group, Defined Names drop-down, Apply Names"]
["Example 18: Defined Names."]
["Goal: Use Defined Names to jump to other places in Excel Workbook File."]
[]
[]
[]
["Example 19: Table Formula Nomenclature (Structured References)."]
["Goal: Add Sales. Add Sales for each Product."]
["Example 21: Add column to Excel Table. Relative Reference in Table Formula Nomenclature."]
["Goal: Add column to Excel Table for Commission Calculation."]
[]
["Sales","Product","Comm ($)",null,"Sales"]
[1140,"Quad",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":31.35},null,{"$f":"SUM(SalesTable[Sales])","v":14570}]
[2501,"Sunset",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":68.78}]
[1412,"Yanaki",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":38.83},null,"Product","Sales"]
[1168,"Quad",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":32.119999999999997},null,"Quad",{"$f":"SUMIFS(SalesTable[Sales],SalesTable[Product],E288)","v":3637}]
[1329,"Quad",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":36.549999999999997},null,"Sunset",{"$f":"SUMIFS(SalesTable[Sales],SalesTable[Product],E289)","v":6186}]
[1298,"Yanaki",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":35.700000000000003},null,"Yanaki",{"$f":"SUMIFS(SalesTable[Sales],SalesTable[Product],E290)","v":4747}]
[2394,"Sunset",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":65.84}]
[1291,"Sunset",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":35.5},null,"Comm Rate"]
[2037,"Yanaki",{"$f":"ROUND(SalesTable[[#This Row],[Sales]]*$E$293,2)","v":56.02},null,2.75E-2]
[]
[]
[]
[]
["Example 22: Mixed Reference in Table Formula Nomenclature."]
["Goal: Add Defects for each month."]
[]
["Number Defects for Each Product:",null,null,null,null,"Total Defects:"]
[null,null,null,null,null,"Jan","Feb","Mar","Apr"]
["Product","Jan","Feb","Mar",null,{"$f":"SUM(DefectsTable[Jan])","v":70},{"$f":"SUM(DefectsTable[Feb])","v":30},{"$f":"SUM(DefectsTable[Mar])","v":39},{"$f":"SUM(DefectsTable[Product])","v":0}]
["Quad",11,1,6]
["Carlota",28,5,9]
["Sunshine",9,0,1]
["Sunset",17,11,23]
["Majestic Beaut",5,13,0]
[]
["Example 23: Table Formula Nomenclature for VLOOKUP and Data Validation."]
["Goal: Create Dynamic Lookup Table."]
[]
["Invoice: 10065"]
["Product","Units","Price","Total",null,null,"Product","Price"]
["Quad",5,{"$f":"_xlfn.IFNA(VLOOKUP(A315,ProductLookupTable[],2,0),\"\")","v":43},{"$f":"IFERROR(ROUND(B315*C315,2),\"\")","v":215},null,null,"Quad",43]
["Carlota",10,{"$f":"_xlfn.IFNA(VLOOKUP(A316,ProductLookupTable[],2,0),\"\")","v":26.95},{"$f":"IFERROR(ROUND(B316*C316,2),\"\")","v":269.5},null,null,"Carlota",26.95]
["Aspen",25,{"$f":"_xlfn.IFNA(VLOOKUP(A317,ProductLookupTable[],2,0),\"\")","v":25},625,null,null,"Sunshine",19.95]
[null,null,{"$f":"_xlfn.IFNA(VLOOKUP(A318,ProductLookupTable[],2,0),\"\")","v":""},"",null,null,"Aspen",25]
[null,null,"Sub Total:",{"$f":"SUM(D315:D318)","v":1109.5}]
[]
[]
[]
["Example 24: Mixed & Absolute Reference in Table Formula Nomenclature."]
["Goal: Add Defects for each month."]
[]
["To lock a field you can use the syntax: [[Field]:[Field]]"]
[]
["Dog","Date","Invoice Number","Category","Amount",null,"Dogs","Food","Accessories","Boarding"]
["Mandy","2013-07-23",10590,"Food",36.799999999999997,null,"Fido",{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G329,DogStoreTable[[Category]:[Category]],H$328)","v":17.8},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G329,DogStoreTable[[Category]:[Category]],I$328)","v":57.5},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G329,DogStoreTable[[Category]:[Category]],J$328)","v":96}]
["Mandy","2013-07-23",10590,"Accessories",22,null,"Fluffy",{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G330,DogStoreTable[[Category]:[Category]],H$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G330,DogStoreTable[[Category]:[Category]],I$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G330,DogStoreTable[[Category]:[Category]],J$328)","v":0}]
["Mandy","2013-07-23",10590,"Accessories",150,null,"Gigi",{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G331,DogStoreTable[[Category]:[Category]],H$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G331,DogStoreTable[[Category]:[Category]],I$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G331,DogStoreTable[[Category]:[Category]],J$328)","v":0}]
["Mandy","2013-07-23",10590,"Boarding",320,null,"Thomas",{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G332,DogStoreTable[[Category]:[Category]],H$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G332,DogStoreTable[[Category]:[Category]],I$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G332,DogStoreTable[[Category]:[Category]],J$328)","v":0}]
["Mandy","2013-07-23",10590,"Food",6.3,null,"Wawa",{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G333,DogStoreTable[[Category]:[Category]],H$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G333,DogStoreTable[[Category]:[Category]],I$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G333,DogStoreTable[[Category]:[Category]],J$328)","v":0}]
["Fido","2013-07-23",10591,"Food",17.8,null,"Mandy",{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G334,DogStoreTable[[Category]:[Category]],H$328)","v":43.099999999999994},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G334,DogStoreTable[[Category]:[Category]],I$328)","v":172},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G334,DogStoreTable[[Category]:[Category]],J$328)","v":320}]
["Fido","2013-07-23",10591,"Accessories",25,null,"May Won",{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G335,DogStoreTable[[Category]:[Category]],H$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G335,DogStoreTable[[Category]:[Category]],I$328)","v":0},{"$f":"SUMIFS(DogStoreTable[[Amount]:[Amount]],DogStoreTable[[Dog]:[Dog]],$G335,DogStoreTable[[Category]:[Category]],J$328)","v":0}]
["Fido","2013-07-23",10591,"Accessories",32.5]
["Fido","2013-07-23",10591,"Boarding",96]

{"sheet":"C AR","index":2,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
["Controlling AR Account"]
["DR","CR"]
[1682]
[154]
[{"$f":"SUM(A3:A4)","v":1836}]

{"sheet":"Assumption Sheet","index":3,"n_rows":2,"n_cols":2,"rows_kept":2,"cut":false,"hidden":false,"merged":[]}
["Jan Commission Rate","Feb Commission Rate"]
[2.75E-2,3.2750000000000001E-2]

{"sheet":"Oak","index":4,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Oak Unit Totals"]
[null,"Jan","Feb","Mar","Apr","Total"]
["Quad",14,8,0,1,{"$f":"SUM(B3:E3)","v":23}]
["Sunset",8,8,2,11,29]
["Carlota",6,6,13,7,32]
["Aspen",13,12,5,2,32]
["Yanaki",9,5,10,1,25]
["Tri-Fly",9,3,2,12,26]
["Total",{"$f":"SUM(B3:B8)","v":59},{"$f":"SUM(C3:C8)","v":42},{"$f":"SUM(D3:D8)","v":32},{"$f":"SUM(E3:E8)","v":34},167]

{"sheet":"Sea","index":5,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Sea Unit Totals"]
[null,"Jan","Feb","Mar","Apr","Total"]
["Quad",9,3,14,9,{"$f":"SUM(B3:E3)","v":35}]
["Sunset",0,8,13,2,23]
["Carlota",13,7,2,7,29]
["Aspen",1,13,3,5,22]
["Yanaki",3,12,10,11,36]
["Tri-Fly",3,0,2,11,16]
["Total",{"$f":"SUM(B3:B8)","v":29},{"$f":"SUM(C3:C8)","v":43},{"$f":"SUM(D3:D8)","v":44},{"$f":"SUM(E3:E8)","v":45},161]

{"sheet":"Tac","index":6,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Tac Unit Totals"]
[null,"Jan","Feb","Mar","Apr","Total"]
["Quad",8,14,0,7,{"$f":"SUM(B3:E3)","v":29}]
["Sunset",4,13,6,14,37]
["Carlota",14,2,2,6,24]
["Aspen",4,8,2,0,14]
["Yanaki",6,5,7,0,18]
["Tri-Fly",12,5,2,5,24]
["Total",{"$f":"SUM(B3:B8)","v":48},{"$f":"SUM(C3:C8)","v":47},{"$f":"SUM(D3:D8)","v":19},{"$f":"SUM(E3:E8)","v":32},146]

{"sheet":"TFN on Diff Sheet","index":7,"n_rows":6,"n_cols":5,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
["Example 20: Table Formula Nomenclature from Different Sheet."]
["Goal: Add Sales for each Product. Count Fields in Table."]
[null,"Product","Sales",null,"Count Fields in Table"]
[null,"Quad",{"$f":"SUMIFS(SalesTable[Sales],SalesTable[Product],B4)","v":3637},null,{"$f":"COUNTA(SalesTable[#Headers])","v":3}]
[null,"Sunset",{"$f":"SUMIFS(SalesTable[Sales],SalesTable[Product],B5)","v":6186}]
[null,"Yanaki",{"$f":"SUMIFS(SalesTable[Sales],SalesTable[Product],B6)","v":4747}]

{"sheet":"Cash","index":8,"n_rows":13,"n_cols":9,"rows_kept":13,"cut":false,"hidden":false,"merged":[[1,9,2,9],[0,6,0,7],[1,0,2,0],[1,3,2,3],[1,4,2,4],[1,5,2,5],[1,6,2,6],[1,7,1,8],[0,0,0,2],[0,3,0,5],[1,1,2,2]]}
["Account:",null,null,"Cash - BECU",null,null,"Account Number:",null,100]
[null,"Date",null,"Item","Post. Ref.","Debit","Credit","Balance"]
[null,null,null,null,null,null,null,"Debit","Credit"]
[null,2014,null,null,null,null,null,"",""]
[null,"March","1900-01-01","Balance","√",14238.82,null,14238.82,""]
[null,null,"2014-03-31",null,"CR 55",37345.619999999995,null,51584.439999999995,""]
[null,null,"2014-03-31",null,"CP 39",null,32721.75,18862.689999999995,""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]

{"sheet":"AR","index":9,"n_rows":13,"n_cols":9,"rows_kept":13,"cut":false,"hidden":false,"merged":[[1,9,2,9],[0,0,0,2],[0,3,0,5],[0,6,0,7],[1,0,2,0],[1,1,2,2],[1,3,2,3],[1,4,2,4],[1,5,2,5],[1,6,2,6],[1,7,1,8]]}
["Account:",null,null,"Accounts Receivable",null,null,"Account Number:",null,120]
[null,"Date",null,"Item","Post. Ref.","Debit","Credit","Balance"]
[null,null,null,null,null,null,null,"Debit","Credit"]
[null,2014,null,null,null,null,null,"",""]
[null,"March","1900-01-01","Balance","√",15398.35,null,15398.35,""]
[null,null,"2014-03-17",null,"J  90",null,27,15371.35,""]
[null,null,"2014-03-31",null,"S 43",45170,null,60541.35,""]
[null,null,"2014-03-31",null,"CR 55",null,33341.35,27200,""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]
[null,null,null,null,null,null,null,"",""]

{"sheet":"Homework==>>","index":10,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[null,"Homework sheets are to the RIGHT ==>> Names like: HW(1), HW(2) and so on…"]

{"sheet":"HW(1)","index":11,"n_rows":15,"n_cols":8,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
[null,"1) Cell C5 has the formula for calculating Retail Selling Price based on Margin. Put the formula in cell C5 in edit mode and add the correct dollar signs to the cell references to enable you to copy the formula through the range C5:E10.\n2) Create a formula in cell F5 that can be copied through the range F5:H10 that will increased each price in the range C5:E10 by 10%."]
[]
[null,null,"Retail Selling Price by Margin"]
["Cost","Items",{"$f":"C14","v":0.45},{"$f":"C4+$C15","v":0.5},{"$f":"D4+$C15","v":0.55000000000000004},"New Price","New Price","New Price"]
[598,"Sofa",{"$f":"A5/(1-C4)","v":1087.2727272727273}]
[85,"Head Lamp"]
[152,"Table"]
[99,"Chair"]
[575,"Dining Set"]
[25,"Picture"]
[]
[null,"Assumption Table"]
[null,"Increase in Price",1.05]
[null,{"$f":"C3&\" Starting Point\"","v":"Retail Selling Price by Margin Starting Point"},0.45]
[null,"Increment",0.05]

{"sheet":"HW(1an)","index":12,"n_rows":15,"n_cols":8,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
[null,"1) Cell C5 has the formula for calculating Retail Selling Price based on Margin. Put the formula in cell C5 in edit mode and add the correct dollar signs to the cell references to enable you to copy the formula through the range C5:E10.\n2) Create a formula in cell F5 that can be copied through the range F5:H10 that will increased each price in the range C5:E10 by 10%."]
[]
[null,null,"Retail Selling Price by Margin"]
["Cost","Items",{"$f":"C14","v":0.45},{"$f":"C4+$C15","v":0.5},{"$f":"D4+$C15","v":0.55000000000000004},"New Price","New Price","New Price"]
[598,"Sofa",{"$f":"$A5/(1-C$4)","v":1087.2727272727273},1196,1328.8888888888889,{"$f":"C5*$C$13","v":1196},{"$f":"D5*$C$13","v":1315.6000000000001},1461.7777777777778]
[85,"Head Lamp",154.54545454545453,170,188.88888888888891,{"$f":"C6*$C$13","v":170},187.00000000000003,207.77777777777783]
[152,"Table",276.36363636363632,304,337.77777777777783,304,334.40000000000003,371.55555555555566]
[99,"Chair",179.99999999999997,198,220.00000000000003,197.99999999999997,217.8,242.00000000000006]
[575,"Dining Set",1045.4545454545453,1150,1277.7777777777778,1150,1265,1405.5555555555557]
[25,"Picture",45.454545454545453,50,55.555555555555564,50,55.000000000000007,61.111111111111128]
[]
[null,"Assumption Table"]
[null,"Increase in Price",1.1000000000000001]
[null,{"$f":"C3&\" Starting Point\"","v":"Retail Selling Price by Margin Starting Point"},0.45]
[null,"Increment",0.05]

{"sheet":"HW(2)","index":13,"n_rows":22,"n_cols":11,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["In the green cells, range D4:J12, create a formula in cell D4 that calculates the expenses as a percent of Income and then copy it through the range D4:J12."]
[]
[null,"Month","Income",{"$f":"D16","v":"Expense 1"},"Expense 2","Expense 3","Expense 4","Expense 5","Expense 6","Expense 7","Total"]
[null,"Jan",5000,null,null,null,null,null,null,null,{"$f":"SUM(D4:J4)","v":0}]
[null,"Feb",6000,null,null,null,null,null,null,null,0]
[null,"Mar",7500,null,null,null,null,null,null,null,0]
[null,"Apr",8000,null,null,null,null,null,null,null,0]
[null,"May",8000,null,null,null,null,null,null,null,0]
[null,"Jun",8000,null,null,null,null,null,null,null,0]
[null,"Jul",10000,null,null,null,null,null,null,null,0]
[null,"Aug",11000,null,null,null,null,null,null,null,0]
[null,"Sep",15000,null,null,null,null,null,null,null,0]
[null,null,"Total",{"$f":"SUM(D4:D12)","v":0},0,0,0,0,0,0,0]
[]
["Assumptions (Formula Inputs)",null,null,"Assumptions (Formula Inputs)"]
["Expense 1",0.1328,null,"Expense 1","Expense 2","Expense 3","Expense 4","Expense 5","Expense 6","Expense 7"]
["Expense 2",0.29270000000000002,null,0.1328,0.29270000000000002,0.2397,0.19550000000000001,0.1215,0.14119999999999999,7.0800000000000002E-2]
["Expense 3",0.2397]
["Expense 4",0.19550000000000001]
["Expense 5",0.1215]
["Expense 6",0.14119999999999999]
["Expense 7",7.0800000000000002E-2]

{"sheet":"HW(2an)","index":14,"n_rows":22,"n_cols":11,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["In the green cells, range D4:J12, create a formula in cell D4 that calculates the expenses as a percent of Income and then copy it through the range D4:J12."]
[]
[null,"Month","Income",{"$f":"D16","v":"Expense 1"},"Expense 2","Expense 3","Expense 4","Expense 5","Expense 6","Expense 7","Total"]
[null,"Jan",5000,{"$f":"ROUND($C4*D$17,2)","v":664},1463.5,1198.5,977.5,607.5,706,354,{"$f":"SUM(D4:J4)","v":5971}]
[null,"Feb",6000,796.8,1756.2,1438.2,1173,729,847.2,424.8,7165.2]
[null,"Mar",7500,996,2195.25,1797.75,1466.25,911.25,1059,531,8956.5]
[null,"Apr",8000,1062.4000000000001,2341.6,1917.6,1564,972,1129.5999999999999,566.4,9553.6]
[null,"May",8000,1062.4000000000001,2341.6,1917.6,1564,972,1129.5999999999999,566.4,9553.6]
[null,"Jun",8000,1062.4000000000001,2341.6,1917.6,1564,972,1129.5999999999999,566.4,9553.6]
[null,"Jul",10000,1328,2927,2397,1955,1215,1412,708,11942]
[null,"Aug",11000,1460.8,3219.7,2636.7,2150.5,1336.5,1553.2,778.8,13136.2]
[null,"Sep",15000,1992,4390.5,3595.5,2932.5,1822.5,2118,1062,17913]
[null,null,"Total",{"$f":"SUM(D4:D12)","v":10424.799999999999},22976.95,18816.45,15346.75,9537.75,11084.2,5557.8,93744.7]
[]
["Assumptions (Formula Inputs)",null,null,"Assumptions (Formula Inputs)"]
["Expense 1",0.1328,null,"Expense 1","Expense 2","Expense 3","Expense 4","Expense 5","Expense 6","Expense 7"]
["Expense 2",0.29270000000000002,null,0.1328,0.29270000000000002,0.2397,0.19550000000000001,0.1215,0.14119999999999999,7.0800000000000002E-2]
["Expense 3",0.2397]
["Expense 4",0.19550000000000001]
["Expense 5",0.1215]
["Expense 6",0.14119999999999999]
["Expense 7",7.0800000000000002E-2]

{"sheet":"HW(3)","index":15,"n_rows":20,"n_cols":11,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["1) Create the formulas to complete the Projected Net Income Statement. Before you start creating formulas, analyze the setup and make adjustments if necessary to be efficient. (You can copy Assumption Table, and use the Paste Special Transpose feature to change the orientation of the table)."]
[]
[null,"Jan","Feb","Mar","Apr","May","Jun"]
["Sales",100,200,300,400,500,600]
["Ex1"]
["Ex2"]
["Ex3"]
["Ex4"]
["Ex5"]
["Ex6"]
["Ex7"]
["Ex8"]
["Ex9"]
["Ex10"]
["Ex11"]
["Total Ex"]
["Net Income"]
[]
["Ex1","Ex2","Ex3","Ex4","Ex5","Ex6","Ex7","Ex8","Ex9","Ex10","Ex11"]
[0.03,0.04,0.05,0.06,7.0000000000000007E-2,0.08,0.09,0.1,0.11,0.12,0.13]

{"sheet":"HW(3an)","index":16,"n_rows":18,"n_cols":10,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["1) Create the formulas to complete the Projected Net Income Statement. Before you start creating formulas, analyze the setup and make adjustments if necessary to be efficient. (You can copy Assumption Table, and use the Paste Special Transpose feature to change the orientation of the table)."]
[]
[null,"Jan","Feb","Mar","Apr","May","Jun"]
["Sales",100,200,300,400,500,600,null,"Assumptions"]
["Ex1",{"$f":"B$4*$J5","v":3},6,9,12,15,18,null,"Ex1",0.03]
["Ex2",4,8,12,16,20,24,null,"Ex2",0.04]
["Ex3",5,10,15,20,25,30,null,"Ex3",0.05]
["Ex4",6,12,18,24,30,36,null,"Ex4",0.06]
["Ex5",7.0000000000000009,14.000000000000002,21.000000000000004,28.000000000000004,35,42.000000000000007,null,"Ex5",7.0000000000000007E-2]
["Ex6",8,16,24,32,40,48,null,"Ex6",0.08]
["Ex7",9,18,27,36,45,54,null,"Ex7",0.09]
["Ex8",10,20,30,40,50,60,null,"Ex8",0.1]
["Ex9",11,22,33,44,55,66,null,"Ex9",0.11]
["Ex10",12,24,36,48,60,72,null,"Ex10",0.12]
["Ex11",13,26,39,52,65,78,null,"Ex11",0.13]
["Total Ex",{"$f":"SUM(B5:B15)","v":88},{"$f":"SUM(C5:C15)","v":176},264,352,440,528,null,null,{"$f":"SUM(J5:J15)","v":0.88}]
["Net Income",{"$f":"B4-B16","v":12},24,36,48,60,72]
[null,null,null,null,null,null,null,null,"Check",{"$f":"G16/G4","v":0.88}]

{"sheet":"HW(4)","index":17,"n_rows":17,"n_cols":14,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["In the green cells create the formulas for expenses, total expenses and net income. Then save the assumptions in a scenario and name it Set 1. Then create a second and third set of assumptions and save them as scenarios and name them set 2 and set 3."]
[]
[null,"Jan","Feb","Mar","Apr","May","Jun",null,"Assumptions",null,null,"Set 2",null,"Set 3"]
["Sales",100,200,300,400,500,600,null,"Ex1",0.03,null,0.04,null,0.02]
["Ex1",null,null,null,null,null,null,null,"Ex2",0.04,null,0.05,null,0.03]
["Ex2",null,null,null,null,null,null,null,"Ex3",0.05,null,0.06,null,0.04]
["Ex3",null,null,null,null,null,null,null,"Ex4",0.06,null,7.0000000000000007E-2,null,0.05]
["Ex4",null,null,null,null,null,null,null,"Ex5",7.0000000000000007E-2,null,0.08,null,0.06]
["Ex5",null,null,null,null,null,null,null,"Ex6",0.08,null,0.09,null,7.0000000000000007E-2]
["Ex6",null,null,null,null,null,null,null,"Ex7",0.09,null,0.1,null,0.08]
["Ex7",null,null,null,null,null,null,null,"Ex8",0.1,null,0.11,null,0.09]
["Ex8",null,null,null,null,null,null,null,"Ex9",0.11,null,0.12,null,0.1]
["Ex9",null,null,null,null,null,null,null,"Ex10",0.12,null,0.13,null,0.11]
["Ex10",null,null,null,null,null,null,null,"Ex11",0.13,null,0.14000000000000001,null,0.12]
["Ex11"]
["Total Ex"]
["Net Income"]

{"sheet":"HW(4an)","index":18,"n_rows":17,"n_cols":10,"rows_kept":17,"cut":false,"hidden":false,"merged":[]}
["In the green cells create the formulas for expenses, total expenses and net income. Then save the assumptions in a scenario and name it Set 1. Then create a second and third set of assumptions and save them as scenarios and name them set 2 and set 3."]
[]
[null,"Jan","Feb","Mar","Apr","May","Jun",null,"Assumptions"]
["Sales",100,200,300,400,500,600,null,"Ex1",0.04]
["Ex1",{"$f":"B$4*$J4","v":4},8,12,16,20,24,null,"Ex2",0.05]
["Ex2",5,10,15,20,25,30,null,"Ex3",0.06]
["Ex3",6,12,18,24,30,36,null,"Ex4",7.0000000000000007E-2]
["Ex4",7.0000000000000009,14.000000000000002,21.000000000000004,28.000000000000004,35,42.000000000000007,null,"Ex5",0.08]
["Ex5",8,16,24,32,40,48,null,"Ex6",0.09]
["Ex6",9,18,27,36,45,54,null,"Ex7",0.1]
["Ex7",10,20,30,40,50,60,null,"Ex8",0.11]
["Ex8",11,22,33,44,55,66,null,"Ex9",0.12]
["Ex9",12,24,36,48,60,72,null,"Ex10",0.13]
["Ex10",13,26,39,52,65,78,null,"Ex11",0.14000000000000001]
["Ex11",14.000000000000002,28.000000000000004,42.000000000000007,56.000000000000007,70,84.000000000000014]
["Total Ex",{"$f":"SUM(B5:B15)","v":99},198,297,396,495,594]
["Net Income",{"$f":"B4-B16","v":1},2,3,4,5,6]

{"sheet":"HW(5)","index":19,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["The projected revenue for Jan, Feb and Mar are $1000, $2000, and $2500 respectively. The projected expenses as a percentage of revenue for each month are: Rent = 25%, Operating Expenses = 35%, Administrative expenses = 10% and Miscellaneous expenses = 8.5%. Create a projected Income Statement and formatted it so it looks good."]

{"sheet":"HW(5an)","index":20,"n_rows":26,"n_cols":8,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
["The projected revenue for Jan, Feb and Mar are $1000, $2000, and $2500 respectively. The projected expenses as a percentage of revenue for each month are: Rent = 25%, Operating Expenses = 35%, Administrative expenses = 10% and Miscellaneous expenses = 8.5%. Create a projected Income Statement and formatted it so it looks good."]
[]
[]
["Month","Rev",{"$f":"C9","v":"Rent"},"Operating Expense","Admin Expenses","Misc. Expenses","Total Expenses","Net Income"]
["Jan",1000,{"$f":"$B5*C$10","v":250},350,100,85,{"$f":"SUM(C5:F5)","v":785},{"$f":"B5-G5","v":215}]
["Feb",2000,500,700,200,170,1570,430]
["Mar",2500,625,875,250,212.50000000000003,1962.5,537.5]
[]
[null,null,"Rent","Operating Expense","Admin Expenses","Misc. Expenses"]
[null,null,0.25,0.35,0.1,8.5000000000000006E-2]
[]
["OR"]
[]
[null,"Jan","Feb","Mar"]
["Rev",1000,2000,2500]
[{"$f":"A23","v":"Rent"},{"$f":"B$15*$B23","v":250},500,625]
[{"$f":"A24","v":"Operating Expense"},350,700,875]
[{"$f":"A25","v":"Admin Expenses"},100,200,250]
[{"$f":"A26","v":"Misc. Expenses"},85,170,212.50000000000003]
["Total Expenses",{"$f":"SUM(B16:B19)","v":785},{"$f":"SUM(C16:C19)","v":1570},{"$f":"SUM(D16:D19)","v":1962.5}]
["Net Income",{"$f":"B15-B20","v":215},{"$f":"C15-C20","v":430},{"$f":"D15-D20","v":537.5}]
[]
["Rent",0.25]
["Operating Expense",0.35]
["Admin Expenses",0.1]
["Misc. Expenses",8.5000000000000006E-2]

{"sheet":"HW(6)","index":21,"n_rows":52,"n_cols":3,"rows_kept":52,"cut":false,"hidden":false,"merged":[]}
["Create a formula in the Earned Commission column using the commission rate from the sheet named \"AssumptionsFor6\"."]
[]
[]
["SalesRep","Sales","Earned Commission"]
["Abraham",1161]
["Hironobu",1321]
["Daniel",1039]
["Dante",1761]
["Sarah B",1561]
["Tonya",1868]
["Janita",2787]
["Debra",1849]
["Tiara",2627]
["Andrea",2165]
["Faye",1589]
["Viktor",2603]
["Na",2366]
["Ryan",2107]
["Damir",2886]
["Sunita",1886]
["Meghan",2177]
["Cynthia",1873]
["Trevor",2164]
["John",1394]
["Sang",2263]
["Dina",2242]
["Tony",1474]
["Nikolay",1998]
["Thanh",2892]
["Tracinda",1873]
["Denis",1954]
["Michael",1729]
["Svitlana Q",1140]
["Leslie",1528]
["Sarah",1659]
["Andra",2119]
["Christien",1854]
["Irina",2759]
["Chandy",1237]
["David",1653]
["Wendy",2604]
["Archie",1479]
["Desiree",2569]
["Denise",1941]
["Hillary",2793]
["Nicolas",2614]
["Ralph",1645]
["Konstanti",1733]
["Svitlana",2466]
["Ewelina",2833]
["Jori",1604]
["Abdullahi",2588]

{"sheet":"AssumptionsFor6","index":22,"n_rows":2,"n_cols":2,"rows_kept":2,"cut":false,"hidden":false,"merged":[]}
["Assumption Table\ncontains Formula Inputs"]
["Commission Rate",0.04]

{"sheet":"HW(6an)","index":23,"n_rows":52,"n_cols":3,"rows_kept":52,"cut":false,"hidden":false,"merged":[]}
["Create a formula in the Earned Commission column using the commission rate from the sheet named \"AssumptionsFor6\"."]
[]
[]
["SalesRep","Sales","Earned Commission"]
["Abraham",1161,{"$f":"B5*AssumptionsFor6!B$2","v":46.44}]
["Hironobu",1321,{"$f":"B6*AssumptionsFor6!B$2","v":52.84}]
["Daniel",1039,{"$f":"B7*AssumptionsFor6!B$2","v":41.56}]
["Dante",1761,{"$f":"B8*AssumptionsFor6!B$2","v":70.44}]
["Sarah B",1561,{"$f":"B9*AssumptionsFor6!B$2","v":62.440000000000005}]
["Tonya",1868,{"$f":"B10*AssumptionsFor6!B$2","v":74.72}]
["Janita",2787,{"$f":"B11*AssumptionsFor6!B$2","v":111.48}]
["Debra",1849,{"$f":"B12*AssumptionsFor6!B$2","v":73.960000000000008}]
["Tiara",2627,{"$f":"B13*AssumptionsFor6!B$2","v":105.08}]
["Andrea",2165,{"$f":"B14*AssumptionsFor6!B$2","v":86.600000000000009}]
["Faye",1589,{"$f":"B15*AssumptionsFor6!B$2","v":63.56}]
["Viktor",2603,{"$f":"B16*AssumptionsFor6!B$2","v":104.12}]
["Na",2366,{"$f":"B17*AssumptionsFor6!B$2","v":94.64}]
["Ryan",2107,{"$f":"B18*AssumptionsFor6!B$2","v":84.28}]
["Damir",2886,{"$f":"B19*AssumptionsFor6!B$2","v":115.44}]
["Sunita",1886,{"$f":"B20*AssumptionsFor6!B$2","v":75.44}]
["Meghan",2177,{"$f":"B21*AssumptionsFor6!B$2","v":87.08}]
["Cynthia",1873,{"$f":"B22*AssumptionsFor6!B$2","v":74.92}]
["Trevor",2164,{"$f":"B23*AssumptionsFor6!B$2","v":86.56}]
["John",1394,{"$f":"B24*AssumptionsFor6!B$2","v":55.76}]
["Sang",2263,{"$f":"B25*AssumptionsFor6!B$2","v":90.52}]
["Dina",2242,{"$f":"B26*AssumptionsFor6!B$2","v":89.68}]
["Tony",1474,{"$f":"B27*AssumptionsFor6!B$2","v":58.96}]
["Nikolay",1998,{"$f":"B28*AssumptionsFor6!B$2","v":79.92}]
["Thanh",2892,{"$f":"B29*AssumptionsFor6!B$2","v":115.68}]
["Tracinda",1873,{"$f":"B30*AssumptionsFor6!B$2","v":74.92}]
["Denis",1954,{"$f":"B31*AssumptionsFor6!B$2","v":78.16}]
["Michael",1729,{"$f":"B32*AssumptionsFor6!B$2","v":69.16}]
["Svitlana Q",1140,{"$f":"B33*AssumptionsFor6!B$2","v":45.6}]
["Leslie",1528,{"$f":"B34*AssumptionsFor6!B$2","v":61.120000000000005}]
["Sarah",1659,{"$f":"B35*AssumptionsFor6!B$2","v":66.36}]
["Andra",2119,{"$f":"B36*AssumptionsFor6!B$2","v":84.76}]
["Christien",1854,{"$f":"B37*AssumptionsFor6!B$2","v":74.16}]
["Irina",2759,{"$f":"B38*AssumptionsFor6!B$2","v":110.36}]
["Chandy",1237,{"$f":"B39*AssumptionsFor6!B$2","v":49.480000000000004}]
["David",1653,{"$f":"B40*AssumptionsFor6!B$2","v":66.12}]
["Wendy",2604,{"$f":"B41*AssumptionsFor6!B$2","v":104.16}]
["Archie",1479,{"$f":"B42*AssumptionsFor6!B$2","v":59.160000000000004}]
["Desiree",2569,{"$f":"B43*AssumptionsFor6!B$2","v":102.76}]
["Denise",1941,{"$f":"B44*AssumptionsFor6!B$2","v":77.64}]
["Hillary",2793,{"$f":"B45*AssumptionsFor6!B$2","v":111.72}]
["Nicolas",2614,{"$f":"B46*AssumptionsFor6!B$2","v":104.56}]
["Ralph",1645,{"$f":"B47*AssumptionsFor6!B$2","v":65.8}]
["Konstanti",1733,{"$f":"B48*AssumptionsFor6!B$2","v":69.320000000000007}]
["Svitlana",2466,{"$f":"B49*AssumptionsFor6!B$2","v":98.64}]
["Ewelina",2833,{"$f":"B50*AssumptionsFor6!B$2","v":113.32000000000001}]
["Jori",1604,{"$f":"B51*AssumptionsFor6!B$2","v":64.16}]
["Abdullahi",2588,{"$f":"B52*AssumptionsFor6!B$2","v":103.52}]

{"sheet":"HW(7)","index":24,"n_rows":25,"n_cols":12,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["In the green cells, range B10:L25, create a formula in cell B10 that calculates the Future Value of a Bank CD where you deposit $4000 at the end of each year for various Annual Rates and Number of Total Years for the CD, and then copy it through the range B10:L25."]
[]
["Amount",4000,null,"**Amount Deposited in Bank CD at the end of Year."]
["Start Annual Rate",0.01]
["Rate Increment",5.0000000000000001E-3]
["Start No. Years",5]
["Year Increment",1]
[]
["Number Years/\nAnnual Rate",{"$f":"B4","v":0.01},{"$f":"B9+$B5","v":1.4999999999999999E-2},0.02,2.5000000000000001E-2,3.0000000000000002E-2,3.5000000000000003E-2,0.04,4.4999999999999998E-2,4.9999999999999996E-2,5.4999999999999993E-2,5.9999999999999991E-2]
[{"$f":"B6","v":5}]
[{"$f":"A10+B$7","v":6}]
[{"$f":"A11+B$7","v":7}]
[8]
[9]
[10]
[11]
[12]
[13]
[14]
[15]
[16]
[17]
[18]
[19]
[{"$f":"A24+B$7","v":20}]

{"sheet":"HW(7an)","index":25,"n_rows":25,"n_cols":12,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["In the green cells, range B10:L25, create a formula in cell B10 that calculates the Future Value of a Bank CD where you deposit $4000 at the end of each year for various Annual Rates and Number of Total Years for the CD, and then copy it through the range B10:L25."]
[]
["Amount",4000,null,"**Amount Deposited in Bank CD at the end of Year."]
["Start Annual Rate",0.01]
["Rate Increment",5.0000000000000001E-3]
["Start No. Years",5]
["Year Increment",1]
[]
["Number Years/\nAnnual Rate",{"$f":"B4","v":0.01},{"$f":"B9+$B5","v":1.4999999999999999E-2},0.02,2.5000000000000001E-2,3.0000000000000002E-2,3.5000000000000003E-2,0.04,4.4999999999999998E-2,4.9999999999999996E-2,5.4999999999999993E-2,5.9999999999999991E-2]
[{"$f":"B6","v":5},{"$f":"FV(B$9,$A10,-$B$3)","v":20404.02003999997},20609.067702499811,20816.160640000002,21025.314062499943,21236.543239999977,21449.863502499942,21665.290240000031,21882.838902499952,22102.525000000016,22324.364102499982,22548.371840000036]
[{"$f":"A10+B$7","v":6},24608.060240400053,24918.20371803725,25232.483852800011,25550.946914062428,25873.639537199986,26200.608725087444,26531.901849600035,26867.566653112423,27207.651250000003,27552.20412813748,27901.274150400041]
[{"$f":"A11+B$7","v":7},28854.140842803932,29291.976773807764,29737.133529855964,30189.720586913998,30649.848723315994,31117.630030465494,31593.177923584026,32076.607152502489,32568.03381250002,33067.575355185036,33575.350599424062]
[8,33142.682251232087,33731.356425414859,34331.876200453102,34944.463601586831,35569.344185015456,36206.747081531757,36856.90504052741,37520.054474365061,38196.435503125009,38886.291999720204,39589.871635389492]
[9,37474.109073744447,38237.326771796041,39018.513724462166,39818.075191626471,40636.424510565921,41473.983229385347,42331.181242148516,43208.456925711478,44106.257278281271,45025.038059704828,45965.263933512862]
[10,41848.850164481897,42810.886673372952,43798.883998951416,44813.527071417135,45855.517245882904,46925.572642413827,48024.428491834457,49152.837487368473,50311.570142195327,51501.415152988586,52723.179769523646]
[11,46267.33866612662,47453.049973473506,48674.861678930407,49933.865248202557,51231.182763259392,52567.967684898314,53945.405631507827,55364.715174300058,56827.148649305105,58333.99298640294,59886.570555695085]
[12,50730.012052787904,52164.845723075538,53648.358912509051,55182.211879407601,56768.118246157144,58407.846553869749,60103.22185676817,61856.12735714353,63668.506081770342,65542.362600655091,67479.764789036795]
[13,55237.312173315797,56947.318408921652,58721.326090759219,60561.767176392786,62471.161793541854,64452.121183255142,66507.350731038896,68639.653088214996,70851.931385858887,73147.192543691126,75528.550676379033]
[14,59789.685295049021,61801.528185055395,63895.752612574426,66075.811355802565,68345.296647348136,70707.945424669088,73167.644760280455,75728.437477184634,78394.527955151789,81170.288133594135,84060.263716961752]
[15,64387.582147999383,66728.551107831183,69173.667664825844,71727.706639697673,74395.655546768583,77182.723514532481,80094.35055069167,83136.217163657944,86314.25435290944,89634.653980941803,93103.87953997952]
[16,69031.457969479525,71729.479374448594,74557.141018122406,77520.899305690094,80627.525213171612,83884.118837541086,87298.124572719375,90877.346936022484,94629.967070554892,98564.559949893592,102690.11231237823]
[17,73721.772549174333,76805.421565065291,80048.283838484873,83458.921788332314,87046.350969566745,90820.06299685502,94790.049555628138,98966.827548143498,103361.46542408266,107985.61074713775,112851.51905112095]
[18,78458.990274666096,81957.502888541247,85649.249515254545,89545.394833040627,93657.741498653762,97998.765201744915,102581.65153785331,107420.33478780992,112529.53869528678,117924.81933823031,123622.61019418821]
[19,83243.580177412645,87186.865431869315,91362.234505559623,95784.029703866647,100467.47374361337,105428.72198380597,110684.91759936743,116254.24985326135,122156.01563005113,128410.68440183296,135039.96680583953]
[{"$f":"A24+B$7","v":20},88076.015979186821,92494.668413347259,97189.479195670836,102178.63044646328,107481.49795592175,113118.72725323915,119112.31430334212,125485.69109665806,132263.81641155368,139473.27204393377,147142.36481418987]

{"sheet":"HW(8)","index":26,"n_rows":8,"n_cols":4,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Create Defined Names for cells B3, B4 and B5 where B3 is named Amount, cell B4 is named Annual_Rate and B5 is named Years. Then in cell B6 calculate the Future Value for a bank CD that allows a deposit at the end of each year of $1,750, and an annual rate of 0.035 and a total number of years of 15. In cell B7 calculate the total amount deposited. In cell B8 calculate the total interest earned."]
[]
["AnswerAmount",1750,null,"**Amount Deposited in Bank CD at the end of Year."]
["AnswerAnnualRate",3.5000000000000003E-2]
["AnswerYears",15]
["Future Value"]
["Total Deposited"]
["Total Interest Earned"]

{"sheet":"HW(8an)","index":27,"n_rows":8,"n_cols":4,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Create Defined Names for cells B3, B4 and B5 where B3 is named Amount, cell B4 is named Annual_Rate and B5 is named Years. Then in cell B6 calculate the Future Value for a bank CD that allows a deposit at the end of each year of $1,750, and an annual rate of 0.035 and a total number of years of 15. In cell B7 calculate the total amount deposited. In cell B8 calculate the total interest earned."]
[]
["AnswerAmount",1750,null,"**Amount Deposited in Bank CD at the end of Year."]
["AnswerAnnualRate",3.5000000000000003E-2]
["AnswerYears",15]
["Future Value",{"$f":"FV(AnswerAnnualRate,AnswerYears,-AnswerAmount)","v":33767.441537607963}]
["Total Deposited",{"$f":"AnswerYears*AnswerAmount","v":26250}]
["Total Interest Earned",{"$f":"B6-B7","v":7517.4415376079633}]

{"sheet":"HW(9)","index":28,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["In the cell C7, create a formula with the SUM function and a 3-D Cell Reference that will add all the Units Sold for each Sales Rep"]
["from the Sheets named \"Sioux\", \"Tyrone\", \"Chin\", \"Phil\", and \"Tommy\". Then copy the formula through the range C7:H12."]
[]
[null,"Units Sold:"]
[]
[null,"Product/Month","Jan","Feb","Mar","Apr","May","Jun"]
[null,"Quad"]
[null,"Sunshine"]
[null,"Aspen"]
[null,"Yanaki"]
[null,"Tri Fly"]
[null,"Majestic Beaut"]

{"sheet":"Sioux","index":29,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[null,"Units Sold:"]
[]
[null,"Product/Month","Jan","Feb","Mar","Apr","May","Jun"]
[null,"Quad",594,224,345,525,514,520]
[null,"Sunshine",379,623,471,679,736,501]
[null,"Aspen",768,352,349,554,453,404]
[null,"Yanaki",689,725,640,380,623,310]
[null,"Tri Fly",679,609,482,562,220,402]
[null,"Majestic Beaut",287,521,780,505,306,713]

{"sheet":"Tyrone","index":30,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[null,"Units Sold:"]
[]
[null,"Product/Month","Jan","Feb","Mar","Apr","May","Jun"]
[null,"Quad",319,241,518,230,256,659]
[null,"Sunshine",659,320,491,507,551,436]
[null,"Aspen",678,214,545,297,729,729]
[null,"Yanaki",746,635,422,256,230,624]
[null,"Tri Fly",238,765,296,438,538,756]
[null,"Majestic Beaut",687,334,534,233,753,646]

{"sheet":"Chin","index":31,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[null,"Units Sold:"]
[]
[null,"Product/Month","Jan","Feb","Mar","Apr","May","Jun"]
[null,"Quad",711,710,767,696,647,526]
[null,"Sunshine",405,240,256,450,263,614]
[null,"Aspen",585,784,338,776,700,450]
[null,"Yanaki",208,759,488,693,417,204]
[null,"Tri Fly",638,292,274,421,521,693]
[null,"Majestic Beaut",332,289,506,239,481,406]

{"sheet":"Phil","index":32,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[null,"Units Sold:"]
[]
[null,"Product/Month","Jan","Feb","Mar","Apr","May","Jun"]
[null,"Quad",303,708,616,711,613,573]
[null,"Sunshine",471,563,275,531,209,356]
[null,"Aspen",645,271,583,415,255,516]
[null,"Yanaki",275,386,726,336,748,288]
[null,"Tri Fly",388,264,340,489,247,324]
[null,"Majestic Beaut",341,380,618,254,524,681]

{"sheet":"Tommy","index":33,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[null,"Units Sold:"]
[]
[null,"Product/Month","Jan","Feb","Mar","Apr","May","Jun"]
[null,"Quad",602,230,505,425,478,735]
[null,"Sunshine",295,751,539,423,625,202]
[null,"Aspen",513,526,452,236,585,486]
[null,"Yanaki",535,558,496,229,346,495]
[null,"Tri Fly",475,644,762,263,479,273]
[null,"Majestic Beaut",431,512,209,272,220,740]

{"sheet":"HW(9an)","index":34,"n_rows":12,"n_cols":8,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["In the cell C7, create a formula with the SUM function and a 3-D Cell Reference that will add all the Units Sold for each Sales Rep"]
["from the Sheets named \"Sioux\", \"Tyrone\", \"Chin\", \"Phil\", and \"Tommy\". Then copy the formula through the range C7:H12."]
[]
[null,"Units Sold:"]
[]
[null,"Product/Month","Jan","Feb","Mar","Apr","May","Jun"]
[null,"Quad",{"$f":"SUM(Sioux:Tommy!C7)","v":2529},{"$f":"SUM(Sioux:Tommy!D7)","v":2113},{"$f":"SUM(Sioux:Tommy!E7)","v":2751},{"$f":"SUM(Sioux:Tommy!F7)","v":2587},{"$f":"SUM(Sioux:Tommy!G7)","v":2508},{"$f":"SUM(Sioux:Tommy!H7)","v":3013}]
[null,"Sunshine",{"$f":"SUM(Sioux:Tommy!C8)","v":2209},{"$f":"SUM(Sioux:Tommy!D8)","v":2497},{"$f":"SUM(Sioux:Tommy!E8)","v":2032},{"$f":"SUM(Sioux:Tommy!F8)","v":2590},{"$f":"SUM(Sioux:Tommy!G8)","v":2384},{"$f":"SUM(Sioux:Tommy!H8)","v":2109}]
[null,"Aspen",{"$f":"SUM(Sioux:Tommy!C9)","v":3189},{"$f":"SUM(Sioux:Tommy!D9)","v":2147},{"$f":"SUM(Sioux:Tommy!E9)","v":2267},{"$f":"SUM(Sioux:Tommy!F9)","v":2278},{"$f":"SUM(Sioux:Tommy!G9)","v":2722},{"$f":"SUM(Sioux:Tommy!H9)","v":2585}]
[null,"Yanaki",{"$f":"SUM(Sioux:Tommy!C10)","v":2453},{"$f":"SUM(Sioux:Tommy!D10)","v":3063},{"$f":"SUM(Sioux:Tommy!E10)","v":2772},{"$f":"SUM(Sioux:Tommy!F10)","v":1894},{"$f":"SUM(Sioux:Tommy!G10)","v":2364},{"$f":"SUM(Sioux:Tommy!H10)","v":1921}]
[null,"Tri Fly",{"$f":"SUM(Sioux:Tommy!C11)","v":2418},{"$f":"SUM(Sioux:Tommy!D11)","v":2574},{"$f":"SUM(Sioux:Tommy!E11)","v":2154},{"$f":"SUM(Sioux:Tommy!F11)","v":2173},{"$f":"SUM(Sioux:Tommy!G11)","v":2005},{"$f":"SUM(Sioux:Tommy!H11)","v":2448}]
[null,"Majestic Beaut",{"$f":"SUM(Sioux:Tommy!C12)","v":2078},{"$f":"SUM(Sioux:Tommy!D12)","v":2036},{"$f":"SUM(Sioux:Tommy!E12)","v":2647},{"$f":"SUM(Sioux:Tommy!F12)","v":1503},{"$f":"SUM(Sioux:Tommy!G12)","v":2284},{"$f":"SUM(Sioux:Tommy!H12)","v":3186}]

{"sheet":"HW(10)","index":35,"n_rows":21,"n_cols":10,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["Create a formula in cell H5 that uses the SUMIFS function to add the Revenue with the two criteria SalesRep and Region, then copy the"]
["formula through the range H5:J9. The Data Set is an Excel Table with the name RevenueTable, so you will need to use Table"]
[]
[]
["Date","SalesRep","Region","Product","Revenue",null,"SalesRep/Region","East","MidWest","West"]
["2015-08-31","Colleen  Warren","West","Quad",159.9,null,"Colleen  Warren"]
["2016-11-27","Arturo  Francis","MidWest","Sunshine",39.9,null,"Arturo  Francis"]
["2015-11-07","Drew  Rogers","West","Crested Beaut",75,null,"Drew  Rogers"]
["2016-06-02","Drew  Rogers","MidWest","Aspen",99,null,"Christy  Olson"]
["2015-08-01","Christy  Olson","MidWest","Quad",45.9,null,"Rachel  Gomez"]
["2016-11-14","Rachel  Gomez","East","Quad",28]
["2015-11-13","Arturo  Francis","MidWest","Aspen",70.5]
["2016-11-04","Christy  Olson","West","Sunshine",45.9]
["2015-09-01","Colleen  Warren","West","Quad",188.68]
["2016-11-28","Arturo  Francis","MidWest","Sunshine",39.1]
["2015-11-08","Drew  Rogers","West","Crested Beaut",70.5]
["2016-06-03","Drew  Rogers","MidWest","Aspen",113.85]
["2015-08-02","Christy  Olson","MidWest","Quad",53.24]
["2016-11-15","Rachel  Gomez","East","Quad",25.2]
["2015-11-14","Arturo  Francis","MidWest","Aspen",86.72]
["2016-11-05","Christy  Olson","West","Sunshine",46.82]

{"sheet":"HW(10an)","index":36,"n_rows":21,"n_cols":11,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["Create a formula in cell H5 that uses the SUMIFS function to add the Revenue with the two criteria SalesRep and Region, then copy the"]
["formula through the range H5:J9. The Data Set is an Excel Table with the name RevenueTable, so you will need to use Table"]
[]
[]
["Date","SalesRep","Region","Product","Revenue",null,"SalesRep/Region","East","MidWest","West"]
["2015-08-31","Colleen  Warren","West","Quad",159.9,null,"Arturo  Francis",{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G6,AnswerRevenueTable[[Region]:[Region]],H$5)","v":0},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G6,AnswerRevenueTable[[Region]:[Region]],I$5)","v":236.22},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G6,AnswerRevenueTable[[Region]:[Region]],J$5)","v":0}]
["2016-11-27","Arturo  Francis","MidWest","Sunshine",39.9,null,"Christy  Olson",{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G7,AnswerRevenueTable[[Region]:[Region]],H$5)","v":0},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G7,AnswerRevenueTable[[Region]:[Region]],I$5)","v":99.14},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G7,AnswerRevenueTable[[Region]:[Region]],J$5)","v":92.72}]
["2015-11-07","Drew  Rogers","West","Crested Beaut",75,null,"Colleen  Warren",{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G8,AnswerRevenueTable[[Region]:[Region]],H$5)","v":0},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G8,AnswerRevenueTable[[Region]:[Region]],I$5)","v":0},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G8,AnswerRevenueTable[[Region]:[Region]],J$5)","v":348.58000000000004}]
["2016-06-02","Drew  Rogers","MidWest","Aspen",99,null,"Drew  Rogers",{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G9,AnswerRevenueTable[[Region]:[Region]],H$5)","v":0},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G9,AnswerRevenueTable[[Region]:[Region]],I$5)","v":212.85},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G9,AnswerRevenueTable[[Region]:[Region]],J$5)","v":145.5}]
["2015-08-01","Christy  Olson","MidWest","Quad",45.9,null,"Rachel  Gomez",{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G10,AnswerRevenueTable[[Region]:[Region]],H$5)","v":53.2},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G10,AnswerRevenueTable[[Region]:[Region]],I$5)","v":0},{"$f":"SUMIFS(AnswerRevenueTable[[Revenue]:[Revenue]],AnswerRevenueTable[[SalesRep]:[SalesRep]],$G10,AnswerRevenueTable[[Region]:[Region]],J$5)","v":0}]
["2016-11-14","Rachel  Gomez","East","Quad",28]
["2015-11-13","Arturo  Francis","MidWest","Aspen",70.5,null,"Check:"]
["2016-11-04","Christy  Olson","West","Sunshine",45.9]
["2015-09-01","Colleen  Warren","West","Quad",188.68,null,"Sum of Revenue","Region"]
["2016-11-28","Arturo  Francis","MidWest","Sunshine",39.1,null,"SalesRep","East","MidWest","West","Grand Total"]
["2015-11-08","Drew  Rogers","West","Crested Beaut",70.5,null,"Arturo  Francis",0,236.22,0,236.22]
["2016-06-03","Drew  Rogers","MidWest","Aspen",113.85,null,"Christy  Olson",0,99.14,92.72,191.86]
["2015-08-02","Christy  Olson","MidWest","Quad",53.24,null,"Colleen  Warren",0,0,348.58000000000004,348.58000000000004]
["2016-11-15","Rachel  Gomez","East","Quad",25.2,null,"Drew  Rogers",0,212.85,145.5,358.35]
["2015-11-14","Arturo  Francis","MidWest","Aspen",86.72,null,"Rachel  Gomez",53.2,0,0,53.2]
["2016-11-05","Christy  Olson","West","Sunshine",46.82,null,"Grand Total",53.2,548.21,586.80000000000007,1188.2100000000003]

{"sheet":"HW(11)","index":37,"n_rows":33,"n_cols":4,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
["Create a formula in cell A7 that that you can copy down and then over through the range A7:D10 that will list"]
["the top 4 Bowling Scores for each Bowler."]
["Use the LARGE function to get and list the top 4 scores and in the k argument use a Formula \"Number"]
["Incrementor\" that will generate the numbers 1, 2, 3, 4 as the formula is copied down across the rows."]
["For example, Chin's scores should be: in cell A10 = 299, in cell A11 = 298, in cell A12 = 265, in cell A13 = 255."]
[]
[]
[]
["Chin","Sue","Gigi","Shela"]
[]
[]
[]
[]
[]
[]
[{"$f":"A9&\"'s Bowling Scores\"","v":"Chin's Bowling Scores"},"Sue's Bowling Scores","Gigi's Bowling Scores","Shela's Bowling Scores"]
[157,187,274,184]
[232,228,267,268]
[231,185,282,259]
[154,224,231,253]
[149,190,160,235]
[180,170,281,297]
[177,222,292,191]
[265,156,205,272]
[208,209,284,207]
[238,262,234,295]
[178,148,234,160]
[196,242,271,221]
[219,234,200,145]
[255,296,148,300]
[298,241,223,223]
[299,148,277,222]
[206,152,162,288]

{"sheet":"HW(11an)","index":38,"n_rows":33,"n_cols":4,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
["Create a formula in cell A7 that that you can copy down and then over through the range A7:D10 that will list"]
["the top 4 Bowling Scores for each Bowler."]
["Use the LARGE function to get and list the top 4 scores and in the k argument use a Formula \"Number"]
["Incrementor\" that will generate the numbers 1, 2, 3, 4 as the formula is copied down across the rows."]
["For example, Chin's scores should be: in cell A10 = 299, in cell A11 = 298, in cell A12 = 265, in cell A13 = 255."]
[]
[]
[]
["Chin","Sue","Gigi","Shela"]
[{"$f":"LARGE(A$17:A$33,ROWS(A$10:A10))","v":299},{"$f":"LARGE(B$17:B$33,ROWS(B$10:B10))","v":296},{"$f":"LARGE(C$17:C$33,ROWS(C$10:C10))","v":292},{"$f":"LARGE(D$17:D$33,ROWS(D$10:D10))","v":300}]
[{"$f":"LARGE(A$17:A$33,ROWS(A$10:A11))","v":298},{"$f":"LARGE(B$17:B$33,ROWS(B$10:B11))","v":262},{"$f":"LARGE(C$17:C$33,ROWS(C$10:C11))","v":284},{"$f":"LARGE(D$17:D$33,ROWS(D$10:D11))","v":297}]
[{"$f":"LARGE(A$17:A$33,ROWS(A$10:A12))","v":265},{"$f":"LARGE(B$17:B$33,ROWS(B$10:B12))","v":242},{"$f":"LARGE(C$17:C$33,ROWS(C$10:C12))","v":282},{"$f":"LARGE(D$17:D$33,ROWS(D$10:D12))","v":295}]
[{"$f":"LARGE(A$17:A$33,ROWS(A$10:A13))","v":255},{"$f":"LARGE(B$17:B$33,ROWS(B$10:B13))","v":241},{"$f":"LARGE(C$17:C$33,ROWS(C$10:C13))","v":281},{"$f":"LARGE(D$17:D$33,ROWS(D$10:D13))","v":288}]
[]
[]
[{"$f":"A9&\"'s Bowling Scores\"","v":"Chin's Bowling Scores"},"Sue's Bowling Scores","Gigi's Bowling Scores","Shela's Bowling Scores"]
[157,187,274,184]
[232,228,267,268]
[231,185,282,259]
[154,224,231,253]
[149,190,160,235]
[180,170,281,297]
[177,222,292,191]
[265,156,205,272]
[208,209,284,207]
[238,262,234,295]
[178,148,234,160]
[196,242,271,221]
[219,234,200,145]
[255,296,148,300]
[298,241,223,223]
[299,148,277,222]
[206,152,162,288]

{"sheet":"HW(12)","index":39,"n_rows":25,"n_cols":4,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Convert the Data Set to an Excel Table and name it \"GrossTable\". Then add a field and name the field"]
["Deduction. Create a formula in the new field that multiplies Tax Rate times Gross Pay. Then add the Gross"]
["Pay column in the cell D6."]
[]
[]
[null,null,"Tax Rate",0.182]
[null,null,"Total Gross Pay"]
[]
[null,"Week","SalesRep","Gross Pay ($)"]
[null,"Week 1","Colleen  Warren",1119.8699999999999]
[null,"Week 1","Arturo  Francis",866.73]
[null,"Week 1","Drew  Rogers",883.26]
[null,"Week 1","Drew  Rogers",686.61]
[null,"Week 1","Christy  Olson",1061.02]
[null,"Week 1","Rachel  Gomez",802.72]
[null,"Week 1","Arturo  Francis",1048.75]
[null,"Week 1","Christy  Olson",1134.6199999999999]
[null,"Week 2","Colleen  Warren",1012.77]
[null,"Week 2","Arturo  Francis",1169.6199999999999]
[null,"Week 2","Drew  Rogers",736.02]
[null,"Week 2","Drew  Rogers",997.28]
[null,"Week 2","Christy  Olson",756.64]
[null,"Week 2","Rachel  Gomez",929.78]
[null,"Week 2","Arturo  Francis",1174.9000000000001]
[null,"Week 2","Christy  Olson",969.78]

{"sheet":"HW(12an)","index":40,"n_rows":25,"n_cols":5,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
["Convert the Data Set to an Excel Table and name it \"GrossTable\". Then add a field and name the field"]
["Deduction. Create a formula in the new field that multiplies Tax Rate times Gross Pay. Then add the Gross"]
["Pay column in the cell D6."]
[]
[]
[null,null,"Tax Rate",0.182]
[null,null,"Total Gross Pay",{"$f":"SUM(AnswerGrossTable[Deduction ($)])","v":2793.7599999999998}]
[]
[null,"Week","SalesRep","Gross Pay ($)","Deduction ($)"]
[null,"Week 1","Colleen  Warren",1119.8699999999999,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":203.82}]
[null,"Week 1","Arturo  Francis",866.73,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":157.74}]
[null,"Week 1","Drew  Rogers",883.26,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":160.75}]
[null,"Week 1","Drew  Rogers",686.61,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":124.96}]
[null,"Week 1","Christy  Olson",1061.02,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":193.11}]
[null,"Week 1","Rachel  Gomez",802.72,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":146.1}]
[null,"Week 1","Arturo  Francis",1048.75,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":190.87}]
[null,"Week 1","Christy  Olson",1134.6199999999999,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":206.5}]
[null,"Week 2","Colleen  Warren",1012.77,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":184.32}]
[null,"Week 2","Arturo  Francis",1169.6199999999999,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":212.87}]
[null,"Week 2","Drew  Rogers",736.02,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":133.96}]
[null,"Week 2","Drew  Rogers",997.28,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":181.5}]
[null,"Week 2","Christy  Olson",756.64,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":137.71}]
[null,"Week 2","Rachel  Gomez",929.78,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":169.22}]
[null,"Week 2","Arturo  Francis",1174.9000000000001,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":213.83}]
[null,"Week 2","Christy  Olson",969.78,{"$f":"ROUND(AnswerGrossTable[[#This Row],[Gross Pay ($)]]*$D$6,2)","v":176.5}]

{"sheet":"HW(13)","index":41,"n_rows":15,"n_cols":8,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["In the range G7:H12 is a lookup table for product price. It is an Excel Table with the name PriceTable."]
["For the Invoice add a Data Validation dropdown List in range A8:A14 based on the first column of the lookup table."]
["For the Invoice add a  VLOOKUP formula to get the price for each product that can be copied down the range C8:C14"]
["without yielding an NA error when there is no product selected in the first column of the Invoice."]
[]
["Invoice: 19045"]
["Product","Units","Price","Total",null,null,"Product","Price"]
["Yanaki",5,null,{"$f":"IFERROR(ROUND(B8*C8,2),\"\")","v":0},null,null,"Aspen",25.95]
["Try Fly",25,null,{"$f":"IFERROR(ROUND(B9*C9,2),\"\")","v":0},null,null,"Yanaki",23.95]
[null,null,null,{"$f":"IFERROR(ROUND(B10*C10,2),\"\")","v":0},null,null,"Try Fly",4.95]
[null,null,null,0,null,null,"FastBoom",34]
[null,null,null,0,null,null,"Long Rang",75]
[null,null,null,0]
[null,null,null,{"$f":"IFERROR(ROUND(B14*C14,2),\"\")","v":0}]
[null,null,"Sub Total:",{"$f":"SUM(D8:D14)","v":0}]

{"sheet":"HW(13an)","index":42,"n_rows":15,"n_cols":8,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["In the range G7:H12 is a lookup table for product price. It is an Excel Table with the name PriceTable."]
["For the Invoice add a Data Validation dropdown List in range A8:A14 based on the first column of the lookup table."]
["For the Invoice add a  VLOOKUP formula to get the price for each product that can be copied down the range C8:C14"]
["without yielding an NA error when there is no product selected in the first column of the Invoice."]
[]
["Invoice: 19045"]
["Product","Units","Price","Total",null,null,"Product","Price"]
["Yanaki",5,{"$f":"_xlfn.IFNA(VLOOKUP(A8,AnswerPriceTable[],2,0),\"\")","v":23.95},{"$f":"IFERROR(ROUND(B8*C8,2),\"\")","v":119.75},null,null,"Aspen",25.95]
["Try Fly",25,{"$f":"_xlfn.IFNA(VLOOKUP(A9,AnswerPriceTable[],2,0),\"\")","v":4.95},{"$f":"IFERROR(ROUND(B9*C9,2),\"\")","v":123.75},null,null,"Yanaki",23.95]
["Long Rang",2,{"$f":"_xlfn.IFNA(VLOOKUP(A10,AnswerPriceTable[],2,0),\"\")","v":75},150,null,null,"Try Fly",4.95]
["Yanaki",15,{"$f":"_xlfn.IFNA(VLOOKUP(A11,AnswerPriceTable[],2,0),\"\")","v":23.95},359.25,null,null,"FastBoom",34]
["Quad",24,{"$f":"_xlfn.IFNA(VLOOKUP(A12,AnswerPriceTable[],2,0),\"\")","v":43},1032,null,null,"Long Rang",75]
[null,null,{"$f":"_xlfn.IFNA(VLOOKUP(A13,AnswerPriceTable[],2,0),\"\")","v":""},"",null,null,"Quad",43]
[null,null,{"$f":"_xlfn.IFNA(VLOOKUP(A14,AnswerPriceTable[],2,0),\"\")","v":""},{"$f":"IFERROR(ROUND(B14*C14,2),\"\")","v":""}]
[null,null,"Sub Total:",{"$f":"SUM(D8:D14)","v":1784.75}]