{"text_format":"1","workbook":"xlsx","n_sheets":15,"sheets":["Topics","Video 02 Reminder","Cliff Notes","Array Formulas","Array Formulas (an)","SizeShape","Homework ==>>","HW(1)","HW(1an)","HW(2)","HW(2an)","HW(3)","HW(3an)","HW(4)","HW(4an)"],"not_rendered":{"charts":0,"images":1,"macros":0,"external_links":0,"defined_names":36,"comments":0,"pivot_tables":4},"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":20,"n_cols":2,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Highline Excel 2016 Class 05: Introduction To Array Formulas"]
[]
["Topics:"]
[1,"Aggregate vs. Array Calculations & Define Array"]
[2,"Define Array Formulas"]
[3,"Example of non-array formula. Standard Formula: Formulas that calculate a single answer. Goal: Calculate Total Expense and Total Net Income."]
[4,"Example of Array Formula"]
[5,"How to decide whether or not you must use Ctrl + Shift + Enter to put the array formula into the cell."]
[6,"Implicit Intersection."]
[7,"SUMPRODUCT function to house an array calculation to avoid using Ctrl + Shift + Enter."]
[8,"Array Formula 2: Math Array Operations. Goal: Calculate Overall Total Expense in a single cell."]
[9,"Array Formula 3: Math Array Operations. Goal: Calculate Overall Net Income in a single cell."]
[10,"Formula 4: Use more than one argument in SUMPRODUCT. Goal: Calculate total Bank Deposit. Learn about SUMPRODUCT and discussion about size and dimensions of arrays in array calculations."]
[11,"Array Formula 5: Function Argument Array Operation in k argument. Goal: Add 3 biggest and 3 smallest. Use Array Constant to avoid Ctrl + Shift + Enter."]
[12,"Array Formula 6: Function Argument Array Operation in criteria1 argument. Goal: Add Sales for each Sales Team. Example of using an Array Formula to create a MUCH smaller formula than non-Array Formula alternatives."]
[13,"No Example 7"]
[14,"Array Formula 8: Function Argument Array Operation in criteria1 argument. Goal: Score Myers-Briggs Test in Human Resource Department."]
[15,"Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation. Goal: Score Myers-Briggs Test in Human Resource Department."]
[16,"List of Functions that can NEVER do Array Calculations."]
[17,"Array Formula 12: Built-in Array Function: FREQUENCY. Goal: Count how many sales are in each category."]

{"sheet":"Video 02 Reminder","index":1,"n_rows":31,"n_cols":6,"rows_kept":31,"cut":false,"hidden":false,"merged":[]}
["Reminder from Video 02:"]
[]
["Ex 10","Goal: Add Calls.",null,null,null,"Aggregate Calculations:"]
[null,"Type of Formula: Aggregate Formula. Number Formula.",null,null,null,"Calculations that take two or more items and"]
[null,"Formula Elements: Equal sign, Built-in Function, Range of Cells.",null,null,null,"calculate a single answer such as adding a column of numbers"]
[]
[null,"Day","Calls Made",null,"Total Calls"]
[null,"Monday",60,null,{"$f":"SUM(C8:C12)","v":190}]
[null,"Tuesday",30]
[null,"Wednesday",25]
[null,"Thursday",35]
[null,"Friday",40]
[]
["Ex 11","Goal: Add Top 3 Bowling Scores For Bowling Tournament.",null,null,null,"Array Calculations:"]
[null,"Type of Formula: Array Formula. Number Formula.",null,null,null,"Calculations that operate on an array of items rather than single items"]
[null,"Formula Elements: Equal sign, Built-in Function (Nested Functions),",null,null,null,"and which deliver an array of answers called a \"resultant array\"."]
[null,"Range of Cells, Array Constant with causes LARGE to deliver 3 numbers."]
[]
[null,null,"Add Top 3","Add Top 3","Add Top 3","Add Top 3"]
[null,null,{"$f":"SUM(LARGE(C23:C31,{1,2,3}))","v":791},{"$f":"SUM(LARGE(D23:D31,{1,2,3}))","v":749},{"$f":"SUM(LARGE(E23:E31,{1,2,3}))","v":879},{"$f":"SUM(LARGE(F23:F31,{1,2,3}))","v":853}]
[]
[null,"Tournament","Scores for Tom","Scores for Debbie","Scores for SheliaDawn","Scores for Chin"]
[null,"Oakland",219,243,163,156]
[null,"SF",235,268,209,285]
[null,"Fremont",207,238,174,292]
[null,"San Jose",183,236,195,276]
[null,"El Cerrito",278,194,284,230]
[null,"Berkeley",160,195,296,257]
[null,"Concord",268,173,207,249]
[null,"Vallejo",245,221,179,223]
[null,"Hayward",233,168,299,213]

{"sheet":"Cliff Notes","index":2,"n_rows":23,"n_cols":1,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
["Definition of Array Formula:"]
["1) Calculation on an array of items rather than single items called an \"Array Calculation\" or \"Array Operation\"."]
["2) Calculation results in an array of answer called \"Resultant Array\"."]
["3) The Resultant Array can be used inside a larger formula, or it can be the final answer."]
["4) Some Array Formulas require you to enter the formula with the special keystroke Ctrl + Shift + Enter in order for it to evaluate to the correct answer."]
["Whether or not you are required to use Ctrl + Shift + Enter is determined by:"]
["[1] The function argument that the Array Calculation sits in."]
["*Five functions that can perform Array Calculations WITHOUT using Ctrl + Shift + Enter:"]
["SUMPRODUCT"]
["AGGREGATE"]
["LOOKUP"]
["INDEX"]
["CHI.SQ.TEST"]
["*All other functions that contain Array Calculations require Ctrl + Shift + Enter."]
["[2] If the arrays being used in the Array Calculation are Array Constants, Ctrl + Shift + Enter is NOT required when you are entering the Array Formula into a single cell ([3] supersedes if you are entering Array Formula into multiple cells)."]
["[3] If you are entering the array of values into multiple cells simultaneously (like with an Array Function) you must use Ctrl + Shift + Enter."]
["How Ctrl + Shift + Enter works:"]
["[1] When you use Ctrl + Shift + Enter, you are telling Excel that the formula is an Array Formula."]
["[2] After you use Ctrl + Shift + Enter, Excel puts curly brackets { } at the beginning and end of the Array Formula to indicate that it understood that you were entering an Array Formula."]
["[3] If you DON'T use Ctrl + Shift + Enter and you are required to, you will get a #VALUE! Error or the wrong answer."]
["5) Specific Array Functions like TRANSPOSE deliver an array of values and require that you highlight a range of cells and then use the key stroke Ctrl + Shift + Enter."]
["6) Array Formulas can be useful because they can allow: 1) compact Excel solutions, shorter formulas and sometimes array formulas are the only way to accomplish the goal."]
["7) Drawbacks for Array Formulas: with large Array Calculations, calculation time for the Excel file can slow down."]

{"sheet":"Array Formulas","index":3,"n_rows":410,"n_cols":25,"rows_kept":410,"cut":false,"hidden":false,"merged":[]}
["Standard Formula: Formulas that calculate a single answer.",null,null,null,null,null,"Definition of Array Formula:"]
["Goal: Calculate Total Expense and Total Net Income.",null,null,null,null,null,"1) Calculation on an array of items rather than single items "]
["Month","Jan","Feb","Mar","Totals",null,"called \"Array Calculation\" or \"Array Operation\"."]
["Revenue",65487,75894,65007,{"$f":"SUM(B4:D4)","v":206388},null,"2) Calculation results in an array of answer"]
["COGS",{"$f":"ROUND(B$4*$B13,2)","v":30422.63},35257.300000000003,30199.64,95879.57,null,"called \"Resultant Array\"."]
["Operating",12756.17,14783.34,12662.67,40202.18,null,"3) The Resultant Array can be used inside a larger formula, or it can be the final answer."]
["Administrative",10110.370000000001,11717.08,10036.27,31863.72,null,"4) Array Formulas require the special keystroke Ctrl + Shift + Enter, except these situations:"]
["Other",2643.8,3063.95,2624.42,8332.17,null,null,"1) One of these five functions:"]
["Total Expenses",{"$f":"SUM(B5:B8)","v":55932.970000000008},{"$f":"SUM(C5:C8)","v":64821.67},{"$f":"SUM(D5:D8)","v":55523},176277.64,null,null,"SUMPRODUCT array argumnet allows Array Calculations with NO Ctrl + Shift + Enter.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1) Five Special Functions that have arguments that do NOT require Ctrl + Shift + Enter"]
["Net Income",{"$f":"B4-B9","v":9554.0299999999916},{"$f":"C4-C9","v":11072.330000000002},{"$f":"D4-D9","v":9484},{"$f":"E4-E9","v":30110.359999999986},null,null,"AGGREGATE array argumnet allows Array Calculations with NO Ctrl + Shift + Enter.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SUMPRODUCT"]
[null,null,null,null,null,null,null,"LOOKUP",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AGGREGATE"]
["Assumption Table (Formula Inputs)",null,null,null,null,null,null,"INDEX",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"LOOKUP"]
["COGS",0.46455983312744287,null,null,null,null,null,"CHI.SQ.TEST",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"INDEX"]
["Operating",0.19478928289353775,null,null,null,null,null,"2) The arrays are Array Constants NO Ctrl + Shift + Enter.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"CHI.SQ.TEST"]
["Administrative",0.15438749265882257,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"** All other function argumnets require Ctrl + Shift + Enter"]
["Other",4.0371412948824396E-2,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2) If you use Array Constant, NOT require Ctrl + Shift + Enter"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"3) Entering Array Formula into multile cells simultaneously will require Ctrl + Shift + Enter"]
["COGS for Jan",null,null,"B4*B13 yields a single answer of 30422.63\n"]
[]
["Array Formula 1: Math Array Operation."]
["Goal: Calculate Total Expense for each month without making calculations in many cells."]
["Month","Jan","Feb","Mar"]
["Revenue",65487,75894,65007,null,"B4*B13:B16 yields an array of answers of {30422.63;12756.17;10110.37;2643.8} (actual array ahs many more decimals)"]
["Total Expenses",null,null,null,null,"Ctrl + Shift + Enter because SUM number1 argument requires it.",null,null,null,null,null,null,null,{"$f":"IF(_xlfn.ISFORMULA(B24),_xlfn.FORMULATEXT(B24),\"\")","v":""}]
["Total Expenses",null,null,null,null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it.",null,null,null,null,null,null,null,{"$f":"IF(_xlfn.ISFORMULA(B25),_xlfn.FORMULATEXT(B25),\"\")","v":""}]
[null,null,null,null,null,"It is all about Function Argument!!! :)"]
["Array Formula 2: Math Array Operations.",null,null,null,null,"When you use Ctrl + Shift + Enter, remember to look in formula bar to verify that curly brackets { } have been entered (only sure way to verify that it will calculate correctly."]
["Goal: Calculate Overall Total Expense in a single cell.",null,null,null,null,"Error you get if you don't use Ctrl + Shift + Enter if it is required: #VALUE!"]
[null,null,null,null,null,"Why Array Formula?"]
["Total Expenses",null,null,"NO Ctrl + Shift + Enter…",null,"Because we did not need all the individual numbers & we did not want to make many calculations in many cells."]
[null,null,null,null,null,"We wanted our Excel solution to be more compact."]
["Array Formula 3: Math Array Operations."]
["Goal: Calculate Overall Net Income in a single cell."]
[]
["Net Income",null,null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it."]
[]
["Formula 4: Use more than one argument in SUMPRODUCT."]
["Goal: Calculate total Bank Deposit."]
[]
["Bill Denomination",1,5,10,20,100]
["Number Bills",16,5,22,43,5]
[]
["Bank Deposit",null,null,{"$f":"IF(_xlfn.ISFORMULA(B43),_xlfn.FORMULATEXT(B43),\"\")","v":""}]
["Bank Deposit",null,null,"",null,null,"If you are multiplying row x row or column x column or table x table (same thing times same thing) the demensions of the arrays must be the same"]
["Bank Deposit",null,null,""]
["Bank Deposit",null,null,""]
[]
["Array Formula 5: Function Argument Array Operation in k argument."]
["Goal: Add 3 biggest and 3 smallest."]
[]
["Add 3 Biggest",null,"Add 3 Fastest",null,"By giving k argument three numbers {1,2,3}, we are telling LARGE and SMALL to deliver 3 answers"]
[null,null,null,null,"NO Ctrl + Shift + Enter because when you use an Array Constant, Ctrl + Shift + Enter is NOT required."]
[]
["Sales",null,"Race Times (min)"]
[1102,null,1.18]
[265,null,1.1299999999999999]
[862,null,1.1599999999999999]
[300,null,1.28]
[1161,null,1.2]
[526,null,1.06]
[1094,null,1.3]
[708,null,1.04]
[1094,null,1.17]
[]
[]
["Array Formula 6: Function Argument Array Operation in criteria1 argument."]
["Goal: Add Sales for each Sales Team."]
[]
["Sales Rep","Sales",null,null,"Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4"]
["Kip",457,null,null,"June","Gigi","Fred","Irene"]
["June",368,null,null,"Sioux","Chin","Kip","Al"]
["Gigi",725,null,null,"Poppi","Sandi","Abdi","Mel"]
["Mel",736,null,null,"Tyrone","Shelia","Pham","Mo"]
["Tyrone",606]
["Chin",1418,null,"Totals",null,null,null,null,null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it."]
["Abdi",1110,null,"Totals",{"$f":"SUMIFS($B$70:$B$111,$A$70:$A$111,E70)+SUMIFS($B$70:$B$111,$A$70:$A$111,E71)+SUMIFS($B$70:$B$111,$A$70:$A$111,E72)+SUMIFS($B$70:$B$111,$A$70:$A$111,E73)","v":9608},{"$f":"SUMIFS($B$70:$B$111,$A$70:$A$111,F70)+SUMIFS($B$70:$B$111,$A$70:$A$111,F71)+SUMIFS($B$70:$B$111,$A$70:$A$111,F72)+SUMIFS($B$70:$B$111,$A$70:$A$111,F73)","v":13631},{"$f":"SUMIFS($B$70:$B$111,$A$70:$A$111,G70)+SUMIFS($B$70:$B$111,$A$70:$A$111,G71)+SUMIFS($B$70:$B$111,$A$70:$A$111,G72)+SUMIFS($B$70:$B$111,$A$70:$A$111,G73)","v":9140},{"$f":"SUMIFS($B$70:$B$111,$A$70:$A$111,H70)+SUMIFS($B$70:$B$111,$A$70:$A$111,H71)+SUMIFS($B$70:$B$111,$A$70:$A$111,H72)+SUMIFS($B$70:$B$111,$A$70:$A$111,H73)","v":4524}]
["Sandi",1489]
["June",175,null,null,"*We give the criteria1 argument 4 criteria, so SUMIFS delivers 4 answers. (this is OR Criteria)"]
["Shelia",1354,null,null,"*We use SUMPRODUCT to add the Resultant Array created by SUMIFS."]
["Pham",132,null,null,"*We choose to create Array formula to avoid longer formula with many SUMIFS."]
["Gigi",449]
["Mo",1333]
["Sandi",1571]
["Chin",1553]
["Tyrone",1196]
["June",679]
["Pham",221]
["Pham",1097]
["Gigi",473]
["Tyrone",573]
["Gigi",1160]
["June",1268]
["Sandi",1635]
["Mel",152]
["Poppi",603]
["Irene",458]
["Sioux",741]
["Fred",486]
["Tyrone",1227]
["Poppi",981]
["Fred",798]
["Sioux",1191]
["Gigi",363]
["Abdi",1332]
["Mo",1289]
["Fred",1625]
["Pham",1261]
["Mo",556]
["Sandi",1441]
["Fred",337]
["Fred",284]
[]
[]
["Array Formula 8: Function Argument Array Operation in criteria1 argument."]
["Goal: Score Myers-Briggs Test in Human Resource Department."]
[]
["Test Results:",null,null,null,"Lookup Table:"]
[null,null,null,null,"Score Myers-Briggs Test"]
["Question","Answer","Join",null,"E","F","I","J","N","P","S","T"]
[1,"a",{"$f":"A120&B120","v":"1a"},null,"11b","12b","11a","18b","10a","18a","10b","12a"]
[2,"a",{"$f":"A121&B121","v":"2a"},null,"15a","14a","15b","23b","13b","23a","13a","14b"]
[3,"b","3b",null,"19a","20b","19b","26a","16b","26b","16a","20a"]
[4,"a","4a",null,"22b","24a","22a","31a","17b","31b","17a","24b"]
[5,"a","5a",null,"27a","25b","27b","4a","1a","4b","1b","25a"]
[6,"b","6b",null,"2b","29a","2a","7a","21b","7b","21a","29b"]
[7,"a","7a",null,"32a","3b","32b","8b","28a","8a","28b","3a"]
[8,"a","8a",null,"6b","5b","6a","9a","30a","9b","30b","5a"]
[9,"b","9b"]
[10,"b","10b",null,"Score:"]
[11,"b","11b",null,"E","F","I","J","N","P","S","T"]
[12,"a","12a",null,null,null,null,null,null,null,null,null,null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it."]
[13,"b","13b"]
[14,"a","14a"]
[15,"a","15a",null,"Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation."]
[16,"a","16a",null,"If we only had raw data for Question and Answer:"]
[17,"b","17b",null,null,null,null,null,null,null,null,null,null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it."]
[18,"b","18b"]
[19,"b","19b"]
[20,"b","20b"]
[21,"b","21b"]
[22,"b","22b"]
[23,"b","23b"]
[24,"b","24b"]
[25,"a","25a"]
[26,"b","26b"]
[27,"b","27b"]
[28,"a","28a"]
[29,"b","29b"]
[30,"a","30a"]
[31,"b","31b"]
[32,"b","32b"]
[]
[]
[]
["Array Formula 10: IF function array formula to filter out values for an aggregate function."]
["Goal: Calculate Average Gross Profit for Each Sales Rep."]
[]
["Sales Rep","Revenue","COGS",null,"Average Gross Profit"]
["Jack",313.19,165.99,null,"Jack",null,null,"Ctrl + Shift + Enter because AVERAGE number1 argument & IF logical_test argument & IF value_if_true arguments all requires it."]
["Tyson",573.69000000000005,200.79,null,"Tyson",null,null,"The \"filter\" to remove values that do not match the criteria = FALSE values."]
["Gigi",67.540000000000006,33.770000000000003,null,"Gigi"]
["Pham",767.4,399.05,null,"Pham"]
["Pham",301.81,126.76]
["Jack",581.33000000000004,342.98]
["Pham",96.09,51.89]
["Pham",269.23,129.22999999999999]
["Tyson",10.57,3.81]
["Tyson",373.23,134.36000000000001]
["Gigi",811.18,413.7]
["Tyson",893.84,429.04]
["Gigi",806.82,306.58999999999997]
["Tyson",559.02,318.64]
["Gigi",700.4,287.16000000000003]
["Jack",530.67999999999995,206.97]
["Tyson",7.8100000000000005,2.73]
["Gigi",284.95,105.43]
["Pham",476.02,180.89]
["Tyson",0.4,0.16]
[]
[]
[]
[]
["Array Formula 11: Built-in Array Function: TRANSPOSE."]
["Goal: Transpose rows and columns so we have Assumption Table that allows Mixed Cell References."]
["Month","Jan","Feb","Mar","Totals"]
["Revenue",65487,75894,65007,{"$f":"SUM(B186:D186)","v":206388}]
["COGS",{"$f":"ROUND(B$186*$G195,2)","v":0},0,0,0]
["Operating",0,0,0,0]
["Administrative",0,0,0,0]
["Other",0,0,0,0]
["Total Expenses",{"$f":"SUM(B187:B190)","v":0},{"$f":"SUM(C187:C190)","v":0},{"$f":"SUM(D187:D190)","v":0},0]
["Net Income",{"$f":"B186-B191","v":65487},{"$f":"C186-C191","v":75894},{"$f":"D186-D191","v":65007},206388]
[]
["Assumption Table (Formula Inputs)",null,null,null,null,"Assumption Table"]
["COGS","Operating","Administrative","Other",null,null,null,null,null,"Ctrl + / to highlight current array."]
[0.46455983312744287,0.19478928289353775,0.15438749265882257,0.05]
[]
["TRANSPOSE function converts a horizontal range to a vertical range, and vice versa."]
["1) Count Rows and Columns = 2 x 4"]
["2) Highlight range that is 4 x 2 (rows and columns)"]
["3) Use Array Function TRANSPOSE"]
["4) Must use Ctrl + Shift + Enter because we are entering an array into multiple cells simultaneously."]
["**Note Array of values is considered a single unit. You cannot delete one cell in the array, or insert a row. The only way to delete is to delete the whole thing."]
[]
[]
["Array Formula 12: Built-in Array Function: FREQUENCY."]
["Goal: Count how many sales are in each category."]
[]
["Sales",null,"Category","Upper Limit","Frequency",null,null,null,"Upper Limits \"hard coded\" into formula:"]
[249.99,null,{"$f":"\"Sales <= \"&DOLLAR(D210,0)","v":"Sales <= $250"},250,null,null,null,null,"Frequency"]
[250,null,{"$f":"DOLLAR(D210,0)&\" < Sales <= \"&DOLLAR(D211,0)","v":"$250 < Sales <= $500"},500,null,null,null,null,{"$f":"FREQUENCY(A210:A222,{250;500;750;1000})","v":2}]
[250.01,null,"$500 < Sales <= $750",750,null,null,null,null,4]
[450,null,"$750 < Sales <= $1,000",1000,null,null,null,null,1]
[499.99,null,{"$f":"\"Sales > \"&DOLLAR(D213,0)","v":"Sales > $1,000"},null,null,null,null,null,5]
[500,null,null,null,null,null,null,null,1]
[500.01,null,"FREQUENCY counts how many numbers are in each category."]
[800,null,"The bins_array argument contains the upper values for the categories—numbers only."]
[900,null,"The data_array argument contains the values to count—numbers only."]
[850,null,"FREQUENCY always creates one more category than the number of upper limits you give it."]
[800,null,"Steps:"]
[1000,null,"1) Highlight range that is row taller than there are Upper Limits."]
[2000,null,"2) Use Array Function FREQUENCY"]
[null,null,"3) Must use Ctrl + Shift + Enter because we are entering an array into multiple cells simultaneously."]
[]
[]
[]
[]
[]
["Array Formula 13: Comparative Array Operation."]
["Goal: Get Min for each City."]
[]
["Location","Time (Minutes) To Assemble Product",null,"Location","Min Time"]
["Seattle",6.7,null,"Seattle",{"$f":"MIN(IF($A$233:$A$392=D233,$B$233:$B$392))","v":4.2},null,"Ctrl + Shift + Enter because MIN number1 argument & IF logical_test argument requires it."]
["Oakland",5.4,null,"Oakland",{"$f":"MIN(IF($A$233:$A$392=D234,$B$233:$B$392))","v":4},null,"The \"filter\" to remove values that do not match the criteria = FALSE values."]
["Oakland",4.7,null,"SF",{"$f":"MIN(IF($A$233:$A$392=D235,$B$233:$B$392))","v":0}]
["SF",7.7]
["SF",5.2,null,"Location","Min Time"]
["SF",9.5,null,"Seattle",{"$f":"_xlfn.AGGREGATE(15,6,$B$233:$B$392/($A$233:$A$392=D238),1)","v":4.2},null,"AGGREGATE function was introduced in Excel 2010. Functions 14-19 can perform Array Calculations with NO Ctrl + Shift + Enter."]
["Oakland",4.5999999999999996,null,"Oakland",{"$f":"_xlfn.AGGREGATE(15,6,$B$233:$B$392/($A$233:$A$392=D239),1)","v":4},null,"The \"filter\" to remove values that do not match the criteria = #DIV/0! values."]
["Oakland",4.4000000000000004,null,"SF",{"$f":"_xlfn.AGGREGATE(15,6,$B$233:$B$392/($A$233:$A$392=D240),1)","v":5.2}]
["Seattle",5.9]
["Oakland",6.7,null,"Location","Min Time"]
["Oakland",6.3,null,"Seattle",{"$f":"_xlfn.MINIFS($B$233:$B$392,$A$233:$A$392,D243)","v":4.2},null,"MINIFS function was introduced in the \"Insider Program\" Excel 2016."]
["Oakland",5.6,null,"Oakland",{"$f":"_xlfn.MINIFS($B$233:$B$392,$A$233:$A$392,D244)","v":4},null,"This is not an array formula. It is similar to SUMIFS and COUNTIFS."]
["SF",5.7,null,"SF",{"$f":"_xlfn.MINIFS($B$233:$B$392,$A$233:$A$392,D245)","v":5.2}]
["SF",6.2]
["Oakland",4.5,null,"Location","Standard Deviation Time"]
["SF",7.9,null,"Seattle",{"$f":"_xlfn.STDEV.S(IF($A$233:$A$392=D248,$B$233:$B$392))","v":0.86857945641652701}]
["SF",5.2,null,"Oakland",{"$f":"_xlfn.STDEV.S(IF($A$233:$A$392=D249,$B$233:$B$392))","v":0.83669608091889158}]
["SF",7.2,null,"SF",{"$f":"_xlfn.STDEV.S(IF($A$233:$A$392=D250,$B$233:$B$392))","v":1.4061981856159698}]
["Seattle",6.7]
["Oakland",5.2]
["SF",7.9]
["SF",6]
["Seattle",4.2]
["SF",7.9]
["Seattle",6.3]
["SF",9.4]
["SF",7.4]
["Seattle",6.4]
["SF",9]
["SF",5.9]
["Seattle",6.9]
["Oakland",5.7]
["SF",7.1]
["Seattle",4.4000000000000004]
["SF",8.9]
["Seattle",6.6]
["SF",9.1]
["SF",7.2]
["SF",9.8000000000000007]
["Oakland",4.7]
["Seattle",6.2]
["SF",8.3000000000000007]
["SF",9.9]
["SF",5.7]
["Oakland",6]
["Oakland",6.8]
["Oakland",6.2]
["Oakland",4.3]
["Seattle",4.4000000000000004]
["Oakland",4.8]
["SF",8.9]
["Seattle",5.2]
["Seattle",5.7]
["SF",9.5]
["Seattle",6.1]
["Seattle",6.7]
["Oakland",5]
["Seattle",6.7]
["SF",7.5]
["Seattle",6.1]
["Oakland",4.2]
["SF",9.4]
["Oakland",6.1]
["Seattle",4.2]
["Oakland",4.3]
["Seattle",7]
["Seattle",6.1]
["Oakland",6.7]
["Seattle",6.3]
["Oakland",4.5999999999999996]
["SF",7.3]
["SF",9]
["Oakland",5.8]
["SF",7.4]
["Seattle",5.7]
["Seattle",5.7]
["SF",5.9]
["SF",7.8]
["SF",9.5]
["SF",9.5]
["Oakland",4.2]
["Oakland",4.5999999999999996]
["Oakland",4]
["Seattle",6]
["Oakland",4.5]
["SF",6.7]
["SF",7.6]
["Seattle",6.8]
["SF",10]
["Oakland",4.8]
["SF",6.1]
["SF",7.6]
["SF",8.1999999999999993]
["Oakland",5.4]
["Seattle",6.9]
["Seattle",6.3]
["Oakland",6.1]
["Oakland",6.8]
["Oakland",4.0999999999999996]
["SF",5.9]
["Oakland",4.7]
["Oakland",5.4]
["SF",7]
["Oakland",5.0999999999999996]
["Oakland",5.0999999999999996]
["Oakland",5.8]
["Oakland",5.7]
["SF",5.8]
["Seattle",5.6]
["Seattle",4.8]
["SF",5.4]
["Seattle",5.7]
["Seattle",5.2]
["Seattle",6.5]
["Oakland",4.9000000000000004]
["SF",8.6999999999999993]
["SF",9.6999999999999993]
["Seattle",5.5]
["SF",6.2]
["Seattle",4.2]
["SF",8]
["Oakland",5.7]
["Oakland",5]
["SF",8.6]
["SF",9.3000000000000007]
["Seattle",4.5999999999999996]
["Oakland",5.0999999999999996]
["Seattle",5]
["Oakland",5.4]
["Seattle",6.5]
["Seattle",4.3]
["SF",8.5]
["Seattle",5]
["SF",5.8]
["Oakland",4.8]
["Oakland",4.2]
["Oakland",5.6]
["Seattle",6.6]
["Seattle",6.6]
["SF",9.3000000000000007]
["Oakland",6.8]
["SF",7.6]
["Seattle",6.4]
["Seattle",5.4]
["Seattle",6.5]
["SF",8.1]
["SF",6.7]
["SF",7.5]
["Seattle",7]
["SF",9.6]
["Oakland",6.8]
["Oakland",4.2]
["Oakland",4.4000000000000004]
["SF",8.6]
["Oakland",5.3]
["Oakland",6.5]
["Seattle",6.6]
["Seattle",6.8]
["Seattle",6.6]
["Seattle",4.9000000000000004]
[]
[]
[]
[]
[]
["Array Formula 14: Built-in Array Function: TRANSPOSE."]
["Goal: Calculate Net Income in a single cell."]
[]
["Assumption Table"]
["COGS",0.46455983312744287]
["Operating",0.19478928289353775]
["Administrative",0.15438749265882257]
["Other",4.0371412948824396E-2]
["Jan",65487]
["Feb",75894]
["Mar",65007]
[]
["Net Income",{"$f":"SUM(B406:B408)-SUM(ROUND(TRANSPOSE(B406:B408)*B402:B405,2))","v":30110.360000000015},null,"Must use Ctrl + Shift + Enter because of TRANSPOSE Array Function."]

{"sheet":"Array Formulas (an)","index":4,"n_rows":425,"n_cols":25,"rows_kept":425,"cut":false,"hidden":false,"merged":[]}
["Standard Formula: Formulas that calculate a single answer.",null,null,null,null,null,"Definition of Array Formula:"]
["Goal: Calculate Total Expense and Total Net Income.",null,null,null,null,null,"1) Calculation on an array of items rather than single items "]
["Month","Jan","Feb","Mar","Totals",null,"called \"Array Calculation\" or \"Array Operation\"."]
["Revenue",65487,75894,65007,{"$f":"SUM(B4:D4)","v":206388},null,"2) Calculation results in an array of answer"]
["COGS",{"$f":"ROUND(B$4*$B13,2)","v":30422.63},35257.300000000003,30199.64,95879.57,null,"called \"Resultant Array\"."]
["Operating",12756.17,14783.34,12662.67,40202.18,null,"3) The Resultant Array can be used inside a larger formula, or it can be the final answer."]
["Administrative",10110.370000000001,11717.08,10036.27,31863.72,null,"4) Array Formulas require the special keystroke Ctrl + Shift + Enter, except these situations:"]
["Other",2643.8,3063.95,2624.42,8332.17,null,null,"1) One of these five functions:"]
["Total Expenses",{"$f":"SUM(B5:B8)","v":55932.970000000008},{"$f":"SUM(C5:C8)","v":64821.67},{"$f":"SUM(D5:D8)","v":55523},176277.64,null,null,"SUMPRODUCT array argumnet allows Array Calculations with NO Ctrl + Shift + Enter.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1) Five Special Functions that have arguments that do NOT require Ctrl + Shift + Enter"]
["Net Income",{"$f":"B4-B9","v":9554.0299999999916},{"$f":"C4-C9","v":11072.330000000002},{"$f":"D4-D9","v":9484},{"$f":"E4-E9","v":30110.359999999986},null,null,"AGGREGATE array argumnet allows Array Calculations with NO Ctrl + Shift + Enter.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SUMPRODUCT"]
[null,null,null,null,null,null,null,"LOOKUP",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AGGREGATE"]
["Assumption Table (Formula Inputs)",null,null,null,null,null,null,"INDEX",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"LOOKUP"]
["COGS",0.46455983312744287,null,null,null,null,null,"CHI.SQ.TEST",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"INDEX"]
["Operating",0.19478928289353775,null,null,null,null,null,"2) The arrays are Array Constants NO Ctrl + Shift + Enter.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"CHI.SQ.TEST"]
["Administrative",0.15438749265882257,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"** All other function argumnets require Ctrl + Shift + Enter"]
["Other",4.0371412948824396E-2,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2) If you use Array Constant, NOT require Ctrl + Shift + Enter"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"3) Entering Array Formula into multile cells simultaneously will require Ctrl + Shift + Enter"]
["COGS for Jan",{"$f":"B4*B13","v":30422.629792016851},null,"B4*B13 yields a single answer of 30422.63\n"]
[]
["Array Formula 1: Math Array Operation."]
["Goal: Calculate Total Expense for each month without making calculations in many cells."]
["Month","Jan","Feb","Mar"]
["Revenue",65487,75894,65007,null,"B4*B13:B16 yields an array of answers of {30422.63;12756.17;10110.37;2643.8} (actual array ahs many more decimals)"]
["Total Expenses",{"$f":"SUM(ROUND(B4*$B$13:$B$16,2))","v":55932.970000000008},{"$f":"SUM(ROUND(C4*$B$13:$B$16,2))","v":64821.67},{"$f":"SUM(ROUND(D4*$B$13:$B$16,2))","v":55523},null,"Ctrl + Shift + Enter because SUM number1 argument requires it.",null,null,null,null,null,null,null,{"$f":"IF(_xlfn.ISFORMULA(B24),_xlfn.FORMULATEXT(B24),\"\")","v":"{=SUM(ROUND(B4*$B$13:$B$16,2))}"}]
["Total Expenses",{"$f":"SUMPRODUCT(ROUND(B4*$B$13:$B$16,2))","v":55932.970000000008},{"$f":"SUMPRODUCT(ROUND(C4*$B$13:$B$16,2))","v":64821.67},55523,null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it.",null,null,null,null,null,null,null,{"$f":"IF(_xlfn.ISFORMULA(B25),_xlfn.FORMULATEXT(B25),\"\")","v":"=SUMPRODUCT(ROUND(B4*$B$13:$B$16,2))"}]
[null,null,null,null,null,"It is all about Function Argument!!! :)"]
["Array Formula 2: Math Array Operations.",null,null,null,null,"When you use Ctrl + Shift + Enter, remember to look in formula bar to verify that curly brackets { } have been entered (only sure way to verify that it will calculate correctly."]
["Goal: Calculate Overall Total Expense in a single cell.",null,null,null,null,"Error you get if you don't use Ctrl + Shift + Enter if it is required: #VALUE!"]
[null,null,null,null,null,"Why Array Formula?"]
["Total Expenses",{"$f":"SUMPRODUCT(ROUND(B23:D23*B13:B16,2))","v":176277.63999999998},null,"NO Ctrl + Shift + Enter…",null,"Because we did not need all the individual numbers & we did not want to make many calculations in many cells."]
[null,null,null,null,null,"We wanted our Excel solution to be more compact."]
["Array Formula 3: Math Array Operations."]
["Goal: Calculate Overall Net Income in a single cell."]
[]
["Net Income",{"$f":"SUM(B23:D23)-SUMPRODUCT(ROUND(B23:D23*B13:B16,2))","v":30110.360000000015},null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it."]
[]
["Formula 4: Use more than one argument in SUMPRODUCT."]
["Goal: Calculate total Bank Deposit."]
[]
["Bill Denomination",1,5,10,20,100]
["Number Bills",16,5,22,43,5]
[]
["Bank Deposit",{"$f":"B40*B41+C40*C41+D40*D41+E40*E41+F40*F41","v":1621},null,{"$f":"IF(_xlfn.ISFORMULA(B43),_xlfn.FORMULATEXT(B43),\"\")","v":"=B40*B41+C40*C41+D40*D41+E40*E41+F40*F41"}]
["Bank Deposit",{"$f":"SUMPRODUCT(B40:F40,B41:F41)","v":1621},null,"=SUMPRODUCT(B40:F40,B41:F41)",null,null,"If you are multiplying row x row or column x column or table x table (same thing times same thing) the demensions of the arrays must be the same"]
["Bank Deposit",{"$f":"SUMPRODUCT(B40:E40,B41:F41)","v":{"$error":"#VALUE!"}},null,"=SUMPRODUCT(B40:E40,B41:F41)"]
["Bank Deposit",null,null,""]
[]
["Array Formula 5: Function Argument Array Operation in k argument."]
["Goal: Add 3 biggest and 3 smallest."]
[]
["Add 3 Biggest",null,"Add 3 Fastest",null,"By giving k argument three numbers {1,2,3}, we are telling LARGE and SMALL to deliver 3 answers"]
[{"$f":"SUM(LARGE(A55:A63,{1,2,3}))","v":3357},null,{"$f":"SUM(SMALL(C55:C63,{1,2,3}))","v":3.23},null,"NO Ctrl + Shift + Enter because when you use an Array Constant, Ctrl + Shift + Enter is NOT required."]
[]
["Sales",null,"Race Times (min)"]
[1102,null,1.18]
[265,null,1.1299999999999999]
[862,null,1.1599999999999999]
[300,null,1.28]
[1161,null,1.2]
[526,null,1.06]
[1094,null,1.3]
[708,null,1.04]
[1094,null,1.17]
[]
[]
["Array Formula 6: Function Argument Array Operation in criteria1 argument."]
["Goal: Add Sales for each Sales Team."]
[]
["Sales Rep","Sales",null,null,"Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4"]
["Kip",457,null,null,"June","Gigi","Fred","Irene"]
["June",368,null,null,"Sioux","Chin","Kip","Al"]
["Gigi",725,null,null,"Poppi","Sandi","Abdi","Mel"]
["Mel",736,null,null,"Tyrone","Shelia","Pham","Mo"]
["Tyrone",606]
["Chin",1418,null,"Totals",{"$f":"SUMPRODUCT(SUMIFS($B$70:$B$111,$A$70:$A$111,E70:E73))","v":9608},{"$f":"SUMPRODUCT(SUMIFS($B$70:$B$111,$A$70:$A$111,F70:F73))","v":13631},{"$f":"SUMPRODUCT(SUMIFS($B$70:$B$111,$A$70:$A$111,G70:G73))","v":9140},{"$f":"SUMPRODUCT(SUMIFS($B$70:$B$111,$A$70:$A$111,H70:H73))","v":4524},null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it."]
["Abdi",1110,null,"Totals",{"$f":"SUMIFS($B$70:$B$111,$A$70:$A$111,E70)+SUMIFS($B$70:$B$111,$A$70:$A$111,E71)+SUMIFS($B$70:$B$111,$A$70:$A$111,E72)+SUMIFS($B$70:$B$111,$A$70:$A$111,E73)","v":9608},{"$f":"SUMIFS($B$70:$B$111,$A$70:$A$111,F70)+SUMIFS($B$70:$B$111,$A$70:$A$111,F71)+SUMIFS($B$70:$B$111,$A$70:$A$111,F72)+SUMIFS($B$70:$B$111,$A$70:$A$111,F73)","v":13631},{"$f":"SUMIFS($B$70:$B$111,$A$70:$A$111,G70)+SUMIFS($B$70:$B$111,$A$70:$A$111,G71)+SUMIFS($B$70:$B$111,$A$70:$A$111,G72)+SUMIFS($B$70:$B$111,$A$70:$A$111,G73)","v":9140},{"$f":"SUMIFS($B$70:$B$111,$A$70:$A$111,H70)+SUMIFS($B$70:$B$111,$A$70:$A$111,H71)+SUMIFS($B$70:$B$111,$A$70:$A$111,H72)+SUMIFS($B$70:$B$111,$A$70:$A$111,H73)","v":4524}]
["Sandi",1489]
["June",175,null,null,"*We give the criteria1 argument 4 criteria, so SUMIFS delivers 4 answers. (this is OR Criteria)"]
["Shelia",1354,null,null,"*We use SUMPRODUCT to add the Resultant Array created by SUMIFS."]
["Pham",132,null,null,"*We choose to create Array formula to avoid longer formula with many SUMIFS."]
["Gigi",449]
["Mo",1333]
["Sandi",1571]
["Chin",1553]
["Tyrone",1196]
["June",679]
["Pham",221]
["Pham",1097]
["Gigi",473]
["Tyrone",573]
["Gigi",1160]
["June",1268]
["Sandi",1635]
["Mel",152]
["Poppi",603]
["Irene",458]
["Sioux",741]
["Fred",486]
["Tyrone",1227]
["Poppi",981]
["Fred",798]
["Sioux",1191]
["Gigi",363]
["Abdi",1332]
["Mo",1289]
["Fred",1625]
["Pham",1261]
["Mo",556]
["Sandi",1441]
["Fred",337]
["Fred",284]
[]
[]
["Array Formula 8: Function Argument Array Operation in criteria1 argument."]
["Goal: Score Myers-Briggs Test in Human Resource Department."]
[]
["Test Results:",null,null,null,"Lookup Table:"]
[null,null,null,null,"Score Myers-Briggs Test"]
["Question","Answer","Join",null,"E","F","I","J","N","P","S","T"]
[1,"a",{"$f":"A120&B120","v":"1a"},null,"11b","12b","11a","18b","10a","18a","10b","12a"]
[2,"a",{"$f":"A121&B121","v":"2a"},null,"15a","14a","15b","23b","13b","23a","13a","14b"]
[3,"b","3b",null,"19a","20b","19b","26a","16b","26b","16a","20a"]
[4,"a","4a",null,"22b","24a","22a","31a","17b","31b","17a","24b"]
[5,"a","5a",null,"27a","25b","27b","4a","1a","4b","1b","25a"]
[6,"b","6b",null,"2b","29a","2a","7a","21b","7b","21a","29b"]
[7,"a","7a",null,"32a","3b","32b","8b","28a","8a","28b","3a"]
[8,"a","8a",null,"6b","5b","6a","9a","30a","9b","30b","5a"]
[9,"b","9b"]
[10,"b","10b",null,"Score:"]
[11,"b","11b",null,"E","F","I","J","N","P","S","T"]
[12,"a","12a",null,{"$f":"SUMPRODUCT(COUNTIFS(E120:E127,$C$120:$C$151))","v":4},{"$f":"SUMPRODUCT(COUNTIFS(F120:F127,$C$120:$C$151))","v":3},4,4,6,4,2,5,null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it."]
[13,"b","13b"]
[14,"a","14a"]
[15,"a","15a",null,"Array Formula 9: Function Argument Array Operation in criteria1 argument. Join Array Operation."]
[16,"a","16a",null,"If we only had raw data for Question and Answer:"]
[17,"b","17b",null,{"$f":"SUMPRODUCT(COUNTIFS(E120:E127,$A$120:$A$151&$B$120:$B$151))","v":4},{"$f":"SUMPRODUCT(COUNTIFS(F120:F127,$A$120:$A$151&$B$120:$B$151))","v":3},4,4,6,4,2,5,null,"NO Ctrl + Shift + Enter because SUMPRODUCT array argument does not require it."]
[18,"b","18b"]
[19,"b","19b"]
[20,"b","20b"]
[21,"b","21b"]
[22,"b","22b"]
[23,"b","23b"]
[24,"b","24b"]
[25,"a","25a"]
[26,"b","26b"]
[27,"b","27b"]
[28,"a","28a"]
[29,"b","29b"]
[30,"a","30a"]
[31,"b","31b"]
[32,"b","32b"]
[]
[]
[]
["Array Formula 10: IF function array formula to filter out values for an aggregate function."]
["Goal: Calculate Average Gross Profit for Each Sales Rep."]
[]
["Sales Rep","Revenue","COGS",null,"Average Gross Profit"]
["Jack",313.19,165.99,null,"Jack",{"$f":"AVERAGE(IF($A$159:$A$178=E159,$B$159:$B$178-$C$159:$C$178))","v":236.42},null,"Ctrl + Shift + Enter because AVERAGE number1 argument & IF logical_test argument & IF value_if_true arguments all requires it."]
["Tyson",573.69000000000005,200.79,null,"Tyson",{"$f":"AVERAGE(IF($A$159:$A$178=E160,$B$159:$B$178-$C$159:$C$178))","v":189.86142857142858},null,"The \"filter\" to remove values that do not match the criteria = FALSE values."]
["Gigi",67.540000000000006,33.770000000000003,null,"Gigi",{"$f":"AVERAGE(IF($A$159:$A$178=E161,$B$159:$B$178-$C$159:$C$178))","v":304.84800000000001}]
["Pham",767.4,399.05,null,"Pham",{"$f":"AVERAGE(IF($A$159:$A$178=E162,$B$159:$B$178-$C$159:$C$178))","v":204.54599999999999}]
["Pham",301.81,126.76]
["Jack",581.33000000000004,342.98]
["Pham",96.09,51.89]
["Pham",269.23,129.22999999999999]
["Tyson",10.57,3.81]
["Tyson",373.23,134.36000000000001]
["Gigi",811.18,413.7]
["Tyson",893.84,429.04]
["Gigi",806.82,306.58999999999997]
["Tyson",559.02,318.64]
["Gigi",700.4,287.16000000000003]
["Jack",530.67999999999995,206.97]
["Tyson",7.8100000000000005,2.73]
["Gigi",284.95,105.43]
["Pham",476.02,180.89]
["Tyson",0.4,0.16]
[]
[]
[]
[]
["Array Formula 11: Built-in Array Function: TRANSPOSE."]
["Goal: Transpose rows and columns so we have Assumption Table that allows Mixed Cell References."]
["Month","Jan","Feb","Mar","Totals"]
["Revenue",65487,75894,65007,{"$f":"SUM(B186:D186)","v":206388}]
["COGS",{"$f":"ROUND(B$186*$G195,2)","v":30422.63},35257.300000000003,30199.64,95879.57]
["Operating",12756.17,14783.34,12662.67,40202.17,null,null,null,"COGS",0.46455983312744287]
["Administrative",10110.370000000001,11717.08,10036.27,31863.73,null,null,null,"Operating",0.19478928289353775]
["Other",3274.35,3794.7,3250.35,10319.4,null,null,null,"Administrative",0.15438749265882257]
["Total Expenses",{"$f":"SUM(B187:B190)","v":56563.520000000004},{"$f":"SUM(C187:C190)","v":65552.42},{"$f":"SUM(D187:D190)","v":56148.93},178264.87,null,null,null,"Other",4.0371412948824396E-2]
["Net Income",{"$f":"B186-B191","v":8923.4799999999959},{"$f":"C186-C191","v":10341.580000000002},{"$f":"D186-D191","v":8858.07},28123.129999999997]
[]
["Assumption Table (Formula Inputs)",null,null,null,null,"Assumption Table"]
["COGS","Operating","Administrative","Other",null,{"$f":"TRANSPOSE(A195:D196)","v":"COGS"},0.46455983312744287,null,null,"Ctrl + / to highlight current array."]
[0.46455983312744287,0.19478928289353775,0.15438749265882257,0.05,null,"Operating",0.19478928289353775]
[null,null,null,null,null,"Administrative",0.15438749265882257]
["TRANSPOSE function converts a horizontal range to a vertical range, and vice versa.",null,null,null,null,"Other",0.05]
["1) Count Rows and Columns = 2 x 4"]
["2) Highlight range that is 4 x 2 (rows and columns)"]
["3) Use Array Function TRANSPOSE"]
["4) Must use Ctrl + Shift + Enter because we are entering an array into multiple cells simultaneously."]
["**Note Array of values is considered a single unit. You cannot delete one cell in the array, or insert a row. The only way to delete is to delete the whole thing."]
[]
[]
["Array Formula 12: Built-in Array Function: FREQUENCY."]
["Goal: Count how many sales are in each category."]
[]
["Sales",null,"Category","Upper Limit","Frequency",null,null,null,"Upper Limits \"hard coded\" into formula:"]
[249.99,null,{"$f":"\"Sales <= \"&DOLLAR(D210,0)","v":"Sales <= $250"},250,{"$f":"FREQUENCY(A210:A222,D210:D213)","v":2},null,null,null,"Frequency"]
[250,null,{"$f":"DOLLAR(D210,0)&\" < Sales <= \"&DOLLAR(D211,0)","v":"$250 < Sales <= $500"},500,4,null,null,null,{"$f":"FREQUENCY(A210:A222,{250;500;750;1000})","v":2}]
[250.01,null,"$500 < Sales <= $750",750,1,null,null,null,4]
[450,null,"$750 < Sales <= $1,000",1000,5,null,null,null,1]
[499.99,null,{"$f":"\"Sales > \"&DOLLAR(D213,0)","v":"Sales > $1,000"},null,1,null,null,null,5]
[500,null,null,null,null,null,null,null,1]
[500.01,null,"FREQUENCY counts how many numbers are in each category."]
[800,null,"The bins_array argument contains the upper values for the categories—numbers only."]
[900,null,"The data_array argument contains the values to count—numbers only."]
[850,null,"FREQUENCY always creates one more category than the number of upper limits you give it."]
[800,null,"Steps:"]
[1000,null,"1) Highlight range that is row taller than there are Upper Limits."]
[2000,null,"2) Use Array Function FREQUENCY"]
[null,null,"3) Must use Ctrl + Shift + Enter because we are entering an array into multiple cells simultaneously."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Array Formula 13: Comparative Array Operation."]
["Goal: Get Min for each City."]
[]
["Location","Time (Minutes) To Assemble Product",null,"Location","Min Time"]
["Seattle",6.7,null,"Seattle",{"$f":"MIN(IF($A$248:$A$407=D248,$B$248:$B$407))","v":4.2},null,"Ctrl + Shift + Enter because MIN number1 argument & IF logical_test argument requires it."]
["Oakland",5.4,null,"Oakland",{"$f":"MIN(IF($A$248:$A$407=D249,$B$248:$B$407))","v":4},null,"The \"filter\" to remove values that do not match the criteria = FALSE values."]
["Oakland",4.7,null,"SF",{"$f":"MIN(IF($A$248:$A$407=D250,$B$248:$B$407))","v":0}]
["SF",7.7]
["SF",5.2,null,"Location","Min Time"]
["SF",9.5,null,"Seattle",{"$f":"_xlfn.AGGREGATE(15,6,$B$248:$B$407/($A$248:$A$407=D253),1)","v":4.2},null,"AGGREGATE function was introduced in Excel 2010. Functions 14-19 can perform Array Calculations with NO Ctrl + Shift + Enter."]
["Oakland",4.5999999999999996,null,"Oakland",{"$f":"_xlfn.AGGREGATE(15,6,$B$248:$B$407/($A$248:$A$407=D254),1)","v":4},null,"The \"filter\" to remove values that do not match the criteria = #DIV/0! values."]
["Oakland",4.4000000000000004,null,"SF",{"$f":"_xlfn.AGGREGATE(15,6,$B$248:$B$407/($A$248:$A$407=D255),1)","v":5.2}]
["Seattle",5.9]
["Oakland",6.7,null,"Location","Min Time"]
["Oakland",6.3,null,"Seattle",{"$f":"_xlfn.MINIFS($B$248:$B$407,$A$248:$A$407,D258)","v":4.2},null,"MINIFS function was introduced in the \"Insider Program\" Excel 2016."]
["Oakland",5.6,null,"Oakland",{"$f":"_xlfn.MINIFS($B$248:$B$407,$A$248:$A$407,D259)","v":4},null,"This is not an array formula. It is similar to SUMIFS and COUNTIFS."]
["SF",5.7,null,"SF",{"$f":"_xlfn.MINIFS($B$248:$B$407,$A$248:$A$407,D260)","v":5.2}]
["SF",6.2]
["Oakland",4.5,null,"Location","Standard Deviation Time"]
["SF",7.9,null,"Seattle",{"$f":"_xlfn.STDEV.S(IF($A$248:$A$407=D263,$B$248:$B$407))","v":0.86857945641652701}]
["SF",5.2,null,"Oakland",{"$f":"_xlfn.STDEV.S(IF($A$248:$A$407=D264,$B$248:$B$407))","v":0.83669608091889158}]
["SF",7.2,null,"SF",{"$f":"_xlfn.STDEV.S(IF($A$248:$A$407=D265,$B$248:$B$407))","v":1.4061981856159698}]
["Seattle",6.7]
["Oakland",5.2]
["SF",7.9]
["SF",6]
["Seattle",4.2]
["SF",7.9]
["Seattle",6.3]
["SF",9.4]
["SF",7.4]
["Seattle",6.4]
["SF",9]
["SF",5.9]
["Seattle",6.9]
["Oakland",5.7]
["SF",7.1]
["Seattle",4.4000000000000004]
["SF",8.9]
["Seattle",6.6]
["SF",9.1]
["SF",7.2]
["SF",9.8000000000000007]
["Oakland",4.7]
["Seattle",6.2]
["SF",8.3000000000000007]
["SF",9.9]
["SF",5.7]
["Oakland",6]
["Oakland",6.8]
["Oakland",6.2]
["Oakland",4.3]
["Seattle",4.4000000000000004]
["Oakland",4.8]
["SF",8.9]
["Seattle",5.2]
["Seattle",5.7]
["SF",9.5]
["Seattle",6.1]
["Seattle",6.7]
["Oakland",5]
["Seattle",6.7]
["SF",7.5]
["Seattle",6.1]
["Oakland",4.2]
["SF",9.4]
["Oakland",6.1]
["Seattle",4.2]
["Oakland",4.3]
["Seattle",7]
["Seattle",6.1]
["Oakland",6.7]
["Seattle",6.3]
["Oakland",4.5999999999999996]
["SF",7.3]
["SF",9]
["Oakland",5.8]
["SF",7.4]
["Seattle",5.7]
["Seattle",5.7]
["SF",5.9]
["SF",7.8]
["SF",9.5]
["SF",9.5]
["Oakland",4.2]
["Oakland",4.5999999999999996]
["Oakland",4]
["Seattle",6]
["Oakland",4.5]
["SF",6.7]
["SF",7.6]
["Seattle",6.8]
["SF",10]
["Oakland",4.8]
["SF",6.1]
["SF",7.6]
["SF",8.1999999999999993]
["Oakland",5.4]
["Seattle",6.9]
["Seattle",6.3]
["Oakland",6.1]
["Oakland",6.8]
["Oakland",4.0999999999999996]
["SF",5.9]
["Oakland",4.7]
["Oakland",5.4]
["SF",7]
["Oakland",5.0999999999999996]
["Oakland",5.0999999999999996]
["Oakland",5.8]
["Oakland",5.7]
["SF",5.8]
["Seattle",5.6]
["Seattle",4.8]
["SF",5.4]
["Seattle",5.7]
["Seattle",5.2]
["Seattle",6.5]
["Oakland",4.9000000000000004]
["SF",8.6999999999999993]
["SF",9.6999999999999993]
["Seattle",5.5]
["SF",6.2]
["Seattle",4.2]
["SF",8]
["Oakland",5.7]
["Oakland",5]
["SF",8.6]
["SF",9.3000000000000007]
["Seattle",4.5999999999999996]
["Oakland",5.0999999999999996]
["Seattle",5]
["Oakland",5.4]
["Seattle",6.5]
["Seattle",4.3]
["SF",8.5]
["Seattle",5]
["SF",5.8]
["Oakland",4.8]
["Oakland",4.2]
["Oakland",5.6]
["Seattle",6.6]
["Seattle",6.6]
["SF",9.3000000000000007]
["Oakland",6.8]
["SF",7.6]
["Seattle",6.4]
["Seattle",5.4]
["Seattle",6.5]
["SF",8.1]
["SF",6.7]
["SF",7.5]
["Seattle",7]
["SF",9.6]
["Oakland",6.8]
["Oakland",4.2]
["Oakland",4.4000000000000004]
["SF",8.6]
["Oakland",5.3]
["Oakland",6.5]
["Seattle",6.6]
["Seattle",6.8]
["Seattle",6.6]
["Seattle",4.9000000000000004]
[]
[]
[]
[]
[]
["Array Formula 14: Built-in Array Function: TRANSPOSE."]
["Goal: Calculate Net Income in a single cell."]
[]
["Assumption Table"]
["COGS",0.46455983312744287]
["Operating",0.19478928289353775]
["Administrative",0.15438749265882257]
["Other",4.0371412948824396E-2]
["Jan",65487]
["Feb",75894]
["Mar",65007]
[]
["Net Income",{"$f":"SUM(B421:B423)-SUM(ROUND(TRANSPOSE(B421:B423)*B417:B420,2))","v":30110.360000000015},null,"Must use Ctrl + Shift + Enter because of TRANSPOSE Array Function."]

{"sheet":"SizeShape","index":5,"n_rows":49,"n_cols":8,"rows_kept":49,"cut":false,"hidden":false,"merged":[]}
["Month","Jan","Feb","Mar"]
["Revenue",100,150,200]
[]
["Assumption Table (Formula Inputs)",null,null,"Jan Rev"]
["COGS",0.5,null,100]
["Operating",0.2,null,100]
["Admin",0.1,null,100]
["Other",0.05,null,100]
[]
["Calculation","Formula","Array Operation",null,"Size & Shape of Arrays","The Math That Happens",null,"Resultant Array"]
[{"$f":"SUMPRODUCT(B2*B5:B8)","v":85},{"$f":"_xlfn.FORMULATEXT(A11)","v":"=SUMPRODUCT(B2*B5:B8)"},{"$f":"MID(B11,SEARCH(\"(\",B11)+1,SEARCH(\")\",B11)-SEARCH(\"(\",B11)-1)","v":"B2*B5:B8"},"=","Single Cell x 4 Rows is OKAY","100*0.5 + 100*0.2 + 100*0.1 + 100*0.05","=","{50;20;10;5}"]
[{"$f":"SUMPRODUCT(B5:B8*B2)","v":85},{"$f":"_xlfn.FORMULATEXT(A12)","v":"=SUMPRODUCT(B5:B8*B2)"},{"$f":"MID(B12,SEARCH(\"(\",B12)+1,SEARCH(\")\",B12)-SEARCH(\"(\",B12)-1)","v":"B5:B8*B2"},"=","4 Rows x Single Cell is OKAY","0.5*100 + 0.2*100 + 0.1*100 + 0.05*100","=","{50;20;10;5}"]
[{"$f":"SUMPRODUCT(B5:B8*B2:D2)","v":382.5},"=SUMPRODUCT(B5:B8*B2:D2)","B5:B8*B2:D2","=","4 Rows x 3 Columns OKAY","100*0.5 + 150*0.5 + 200*0.5 + 100*0.2 + 150*0.2 + 200*0.2 + 100*0.1 + 150*0.1 + 200*0.1 + 100*0.05 + 150*0.05 + 200*0.05","=","{50,75,100;20,30,40;10,15,20;5,7.5,10}"]
[{"$f":"SUMPRODUCT(B2:D2*B5:B8)","v":382.5},"=SUMPRODUCT(B2:D2*B5:B8)","B2:D2*B5:B8","="," 3 Columns x 4 Rows OKAY","0.5*100 + 0.5*150 + 0.5*200 + 0.2*100 + 0.2*150 + 0.2*200 + 0.1*100 + 0.1*150 + 0.1*200 + 0.05*100 + 0.05*150 + 0.05*200","=","{50,75,100;20,30,40;10,15,20;5,7.5,10}"]
[{"$f":"SUMPRODUCT(B5:B8*D5:D8)","v":85},"=SUMPRODUCT(B5:B8*D5:D8)","B5:B8*D5:D8","=","4 Rows x 4 Rows Okay","0.5*100 + 0.2*100 + 0.1*100 + 0.05*100","=","{50;20;10;5}"]
[{"$f":"SUMPRODUCT(A21:D21*A23:D23)","v":85},"=SUMPRODUCT(A21:D21*A23:D23)","A21:D21*A23:D23","=","4 Columns by 4 Columns OKAY","0.5*100 + 0.2*100 + 0.1*100 + 0.05*100","=","{50,20,10,5}"]
[{"$f":"SUMPRODUCT(B5:B7*D5:D8)","v":{"$error":"#N/A"}},"=SUMPRODUCT(B5:B7*D5:D8)","B5:B7*D5:D8","=","If multiplying Rows x Rows, they MUST be same size","Error","=","{50;20;10;#N/A}"]
[{"$f":"SUMPRODUCT(A21:C21*A23:D23)","v":{"$error":"#N/A"}},"=SUMPRODUCT(A21:C21*A23:D23)","A21:C21*A23:D23","=","If multiplying Columns x Columns, they MUST be same size","Error","=","{50,20,10,#N/A}"]
[]
["COGS","Operating","Admin","Other"]
[0.5,0.2,0.1,0.05]
["Jan Rev"]
[100,100,100,100]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,"Formula in F11: =TEXTJOIN(\" + \",,A38:A41) (From 2016 Insider Program)"]
[{"$f":"B$2&\"*\"&$B5","v":"100*0.5"},"150*0.5","200*0.5"]
["100*0.2","150*0.2","200*0.2"]
["100*0.1","150*0.1","200*0.1"]
["100*0.05","150*0.05","200*0.05"]
[]
[{"$f":"$B5&\"*\"&B$2","v":"0.5*100"},"0.5*150","0.5*200"]
["0.2*100","0.2*150","0.2*200"]
["0.1*100","0.1*150","0.1*200"]
["0.05*100","0.05*150","0.05*200"]
[]
[]
[{"$f":"B5&\"*\"&D5","v":"0.5*100"}]
[{"$f":"B6&\"*\"&D6","v":"0.2*100"}]
[{"$f":"B7&\"*\"&D7","v":"0.1*100"}]
[{"$f":"B8&\"*\"&D8","v":"0.05*100"}]
[]
[{"$f":"A21&\"*\"&A23","v":"0.5*100"},{"$f":"B21&\"*\"&B23","v":"0.2*100"},{"$f":"C21&\"*\"&C23","v":"0.1*100"},{"$f":"D21&\"*\"&D23","v":"0.05*100"}]

{"sheet":"Homework ==>>","index":6,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"HW(1)","index":7,"n_rows":35,"n_cols":9,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
["Sales Rep","Revenue","COGS",null,"Calculate Gross Profit (Revenue - COGS)  in cell D5:"]
["Jo",1944,1166.4000000000001]
["Mo",1570,628]
["Jo",1012,607.20000000000005]
["Jo",1088,435.2,null,"Calculate Max Gross Profit for Mo and Jo:"]
["Mo",759,303.60000000000002,null,"Sales Rep","Max",null,null,"Max"]
["Jo",484,242,null,"Jo"]
["Mo",827,413.5,null,"Mo",null,null,"or"]
["Mo",704,281.60000000000002]
["Mo",1707,1194.9000000000001]
["Jo",511,255.5,null,"Calculate MIN Gross Profit for Mo and Jo:"]
["Jo",722,505.4,null,"Sales Rep","Min",null,null,"Min"]
["Mo",183,109.8,null,"Jo"]
["Mo",1473,736.5,null,"Mo",null,null,"or"]
["Mo",468,327.60000000000002]
[]
[null,null,null,null,"Calculate Average Gross Profit for Mo and Jo:"]
[null,null,null,null,"Sales Rep","Gross Profit:"]
[null,null,null,null,"Jo"]
[null,null,null,null,"Mo",null,null,"or"]
[]
[]
[null,null,null,null,"Calculate Gross Profit Standard Deviation for Mo and Jo:"]
[null,null,null,null,"Sales Rep","Gross Profit:",null,null,"Gross Profit:"]
[null,null,null,null,"Jo"]
[null,null,null,null,"Mo"]
[]
[]
[null,null,null,null,"Calculate Median Standard Deviation for Mo and Jo:"]
[null,null,null,null,"Sales Rep","Gross Profit:",null,null,"Gross Profit:"]
[null,null,null,null,"Jo"]
[null,null,null,null,"Mo"]

{"sheet":"HW(1an)","index":8,"n_rows":35,"n_cols":9,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
["Sales Rep","Revenue","COGS",null,"Calculate Gross Profit (Revenue - COGS)  in cell D5:"]
["Jo",1944,1166.4000000000001,null,{"$f":"SUMPRODUCT(B5:B18-C5:C18)","v":6244.8}]
["Mo",1570,628]
["Jo",1012,607.20000000000005]
["Jo",1088,435.2,null,"Calculate Max Gross Profit for Mo and Jo:"]
["Mo",759,303.60000000000002,null,"Sales Rep","Max",null,null,"Max"]
["Jo",484,242,null,"Jo",{"$f":"MAX(IF($A$5:$A$18=E10,$B$5:$B$18-$C$5:$C$18))","v":777.59999999999991},null,null,{"$f":"_xlfn.AGGREGATE(14,4,($A$5:$A$18=E10)*($B$5:$B$18-$C$5:$C$18),1)","v":777.59999999999991}]
["Mo",827,413.5,null,"Mo",{"$f":"MAX(IF($A$5:$A$18=E11,$B$5:$B$18-$C$5:$C$18))","v":942},null,"or",{"$f":"_xlfn.AGGREGATE(14,4,($A$5:$A$18=E11)*($B$5:$B$18-$C$5:$C$18),1)","v":942}]
["Mo",704,281.60000000000002]
["Mo",1707,1194.9000000000001]
["Jo",511,255.5,null,"Calculate MIN Gross Profit for Mo and Jo:"]
["Jo",722,505.4,null,"Sales Rep","Min",null,null,"Min"]
["Mo",183,109.8,null,"Jo",{"$f":"MIN(IF($A$5:$A$18=E16,$B$5:$B$18-$C$5:$C$18))","v":216.60000000000002},null,null,{"$f":"_xlfn.AGGREGATE(15,6,($B$5:$B$18-$C$5:$C$18)/($A$5:$A$18=E16),1)","v":216.60000000000002}]
["Mo",1473,736.5,null,"Mo",{"$f":"MIN(IF($A$5:$A$18=E17,$B$5:$B$18-$C$5:$C$18))","v":73.2},null,"or",{"$f":"_xlfn.AGGREGATE(15,6,($B$5:$B$18-$C$5:$C$18)/($A$5:$A$18=E17),1)","v":73.2}]
["Mo",468,327.60000000000002]
[]
[null,null,null,null,"Calculate Average Gross Profit for Mo and Jo:"]
[null,null,null,null,"Sales Rep","Gross Profit:"]
[null,null,null,null,"Jo",{"$f":"AVERAGE(IF($A$5:$A$18=E22,$B$5:$B$18-$C$5:$C$18))","v":424.88333333333327}]
[null,null,null,null,"Mo",{"$f":"AVERAGE(IF($A$5:$A$18=E23,$B$5:$B$18-$C$5:$C$18))","v":461.9375},null,"or"]
[]
[]
[null,null,null,null,"Calculate Gross Profit Standard Deviation for Mo and Jo:"]
[null,null,null,null,"Sales Rep","Gross Profit:",null,null,"Gross Profit:"]
[null,null,null,null,"Jo",{"$f":"_xlfn.STDEV.S(IF($A$5:$A$18=E28,$B$5:$B$18-$C$5:$C$18))","v":237.60360617353155},null,null,{"$f":"SUMPRODUCT(--($A$5:$A$18=E10),$B$5:$B$18-$C$5:$C$18)","v":2549.2999999999997}]
[null,null,null,null,"Mo",{"$f":"_xlfn.STDEV.S(IF($A$5:$A$18=E29,$B$5:$B$18-$C$5:$C$18))","v":284.55885626451942},null,null,{"$f":"SUMPRODUCT(--($A$5:$A$18=E11),$B$5:$B$18-$C$5:$C$18)","v":3695.5}]
[]
[]
[null,null,null,null,"Calculate Median Standard Deviation for Mo and Jo:"]
[null,null,null,null,"Sales Rep","Gross Profit:",null,null,"Gross Profit:"]
[null,null,null,null,"Jo",{"$f":"MEDIAN(IF($A$5:$A$18=E34,$B$5:$B$18-$C$5:$C$18))","v":330.15},null,null,{"$f":"_xlfn.AGGREGATE(16,6,($B$5:$B$18-$C$5:$C$18)/($A$5:$A$18=E34),0.5)","v":330.15}]
[null,null,null,null,"Mo",{"$f":"MEDIAN(IF($A$5:$A$18=E35,$B$5:$B$18-$C$5:$C$18))","v":438.9},null,null,{"$f":"_xlfn.AGGREGATE(16,6,($B$5:$B$18-$C$5:$C$18)/($A$5:$A$18=E35),0.5)","v":438.9}]

{"sheet":"HW(2)","index":9,"n_rows":7,"n_cols":8,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[null,"Using the array function, TRANSPOSE, transpose the table below somewhere on this worksheet."]
[]
[null,"Region/Sales Rep","Jo","Phil","Dim","Chin","Sioux","Timmy"]
[null,"West",2042.75,134.79,2032.99,1045.55,804.55,852.51]
[null,"East",296.64,120.89,1439.23,100.12,1676.61,1732.7]
[null,"South",520.63,785.3,666.5,1356.3,1007.96,178.87]
[null,"NorthWest",1698.67,694.6,166.44,2176.94,608.09,1234.25]

{"sheet":"HW(2an)","index":10,"n_rows":18,"n_cols":8,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
[null,"Using the array function, TRANSPOSE, transpose the table below somewhere on this worksheet."]
[]
[null,"Region/Sales Rep","Jo","Phil","Dim","Chin","Sioux","Timmy"]
[null,"West",2042.75,134.79,2032.99,1045.55,804.55,852.51]
[null,"East",296.64,120.89,1439.23,100.12,1676.61,1732.7]
[null,"South",520.63,785.3,666.5,1356.3,1007.96,178.87]
[null,"NorthWest",1698.67,694.6,166.44,2176.94,608.09,1234.25]
[]
[]
[]
[]
[null,{"$f":"TRANSPOSE(B3:H7)","v":"Region/Sales Rep"},"West","East","South","NorthWest"]
[null,"Jo",2042.75,296.64,520.63,1698.67]
[null,"Phil",134.79,120.89,785.3,694.6]
[null,"Dim",2032.99,1439.23,666.5,166.44]
[null,"Chin",1045.55,100.12,1356.3,2176.94]
[null,"Sioux",804.55,1676.61,1007.96,608.09]
[null,"Timmy",852.51,1732.7,178.87,1234.25]

{"sheet":"HW(3)","index":11,"n_rows":20,"n_cols":4,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Add Top 5 Values for each Month"]
[]
[{"$f":"A7&\" Top 5 Sales\"","v":"Jan Top 5 Sales"},{"$f":"B7&\" Top 5 Sales\"","v":"Feb Top 5 Sales"},"Mar Top 5 Sales","Apr Top 5 Sales"]
[]
[]
[]
["Jan","Feb","Mar","Apr"]
[106303,73170,68152,85474]
[22651,29835,708,4238]
[51521,61995,80411,68484]
[97311,73219,96866,112463]
[15994,64443,41400,86489]
[81925,52387,106117,1100]
[30452,104195,33210,11156]
[75856,3380,65,111051]
[21757,40699,29888,52040]
[31223,74829,75356,73249]
[78304,75657,21713,98459]
[97947,81685,102302,84943]
[48080,52265,35407,119599]

{"sheet":"HW(3an)","index":12,"n_rows":20,"n_cols":4,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Add Top 5 Values for each Month"]
[]
[{"$f":"A7&\" Top 5 Sales\"","v":"Jan Top 5 Sales"},{"$f":"B7&\" Top 5 Sales\"","v":"Feb Top 5 Sales"},"Mar Top 5 Sales","Apr Top 5 Sales"]
[{"$f":"SUM(LARGE(A8:A20,{1,2,3,4,5}))","v":461790},{"$f":"SUM(LARGE(B8:B20,{1,2,3,4,5}))","v":409585},{"$f":"SUM(LARGE(C8:C20,{1,2,3,4,5}))","v":461052},{"$f":"SUM(LARGE(D8:D20,{1,2,3,4,5}))","v":528061}]
[]
[]
["Jan","Feb","Mar","Apr"]
[106303,73170,68152,85474]
[22651,29835,708,4238]
[51521,61995,80411,68484]
[97311,73219,96866,112463]
[15994,64443,41400,86489]
[81925,52387,106117,1100]
[30452,104195,33210,11156]
[75856,3380,65,111051]
[21757,40699,29888,52040]
[31223,74829,75356,73249]
[78304,75657,21713,98459]
[97947,81685,102302,84943]
[48080,52265,35407,119599]

{"sheet":"HW(4)","index":13,"n_rows":45,"n_cols":9,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Create a formula that can add sales based on Sales Team and Region."]
[]
["Sales Rep","Sales","Region",null,null,"Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4"]
["Kip",457,"North America",null,null,"June","Gigi","Fred","Irene"]
["June",368,"North America",null,null,"Sioux","Chin","Kip","Al"]
["Gigi",725,"South America",null,null,"Poppi","Sandi","Abdi","Mel"]
["Mel",736,"South America",null,null,"Tyrone","Shelia","Pham","Mo"]
["Tyrone",606,"South America",null,"North America"]
["Chin",1418,"South America",null,"South America"]
["Abdi",1110,"South America"]
["Sandi",1489,"North America"]
["June",175,"North America"]
["Shelia",1354,"South America"]
["Pham",132,"North America"]
["Gigi",449,"South America"]
["Mo",1333,"North America"]
["Sandi",1571,"North America"]
["Chin",1553,"North America"]
["Tyrone",1196,"South America"]
["June",679,"North America"]
["Pham",221,"South America"]
["Pham",1097,"North America"]
["Gigi",473,"North America"]
["Tyrone",573,"South America"]
["Gigi",1160,"South America"]
["June",1268,"North America"]
["Sandi",1635,"North America"]
["Mel",152,"North America"]
["Poppi",603,"North America"]
["Irene",458,"North America"]
["Sioux",741,"South America"]
["Fred",486,"North America"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America"]
["Fred",798,"North America"]
["Sioux",1191,"South America"]
["Gigi",363,"South America"]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]

{"sheet":"HW(4an)","index":14,"n_rows":45,"n_cols":10,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
["Create a formula that can add sales based on Sales Team and Region."]
[]
["Sales Rep","Sales","Region",null,null,"Sales Team 1","Sales Team 2","Sales Team 3","Sales Team 4"]
["Kip",457,"North America",null,null,"June","Gigi","Fred","Irene"]
["June",368,"North America",null,null,"Sioux","Chin","Kip","Al"]
["Gigi",725,"South America",null,null,"Poppi","Sandi","Abdi","Mel"]
["Mel",736,"South America",null,null,"Tyrone","Shelia","Pham","Mo"]
["Tyrone",606,"South America",null,"North America",{"$f":"SUMPRODUCT(SUMIFS($B$4:$B$45,$C$4:$C$45,$E8,$A$4:$A$45,F$4:F$7))","v":5301},8162,6211,2499]
["Chin",1418,"South America",null,"South America",4307,5469,2929,2025]
["Abdi",1110,"South America"]
["Sandi",1489,"North America",null,"Check:","Sales Rep","(Multiple Items)",null,null,"<<==  Takes Longer to create PivotTAbles"]
["June",175,"North America"]
["Shelia",1354,"South America",null,null,"Row Labels","Sum of Sales"]
["Pham",132,"North America",null,null,"North America",5301]
["Gigi",449,"South America",null,null,"South America",4307]
["Mo",1333,"North America",null,null,"Grand Total",9608]
["Sandi",1571,"North America"]
["Chin",1553,"North America",null,null,null,"Sales Rep","(Multiple Items)"]
["Tyrone",1196,"South America"]
["June",679,"North America",null,null,null,"Row Labels","Sum of Sales"]
["Pham",221,"South America",null,null,null,"North America",8162]
["Pham",1097,"North America",null,null,null,"South America",5469]
["Gigi",473,"North America",null,null,null,"Grand Total",13631]
["Tyrone",573,"South America"]
["Gigi",1160,"South America",null,null,null,null,"Sales Rep","(Multiple Items)"]
["June",1268,"North America"]
["Sandi",1635,"North America",null,null,null,null,"Row Labels","Sum of Sales"]
["Mel",152,"North America",null,null,null,null,"North America",6211]
["Poppi",603,"North America",null,null,null,null,"South America",2929]
["Irene",458,"North America",null,null,null,null,"Grand Total",9140]
["Sioux",741,"South America"]
["Fred",486,"North America",null,null,null,null,null,"Sales Rep","(Multiple Items)"]
["Tyrone",1227,"North America"]
["Poppi",981,"North America",null,null,null,null,null,"Row Labels","Sum of Sales"]
["Fred",798,"North America",null,null,null,null,null,"North America",2499]
["Sioux",1191,"South America",null,null,null,null,null,"South America",2025]
["Gigi",363,"South America",null,null,null,null,null,"Grand Total",4524]
["Abdi",1332,"North America"]
["Mo",1289,"South America"]
["Fred",1625,"North America"]
["Pham",1261,"South America"]
["Mo",556,"North America"]
["Sandi",1441,"North America"]
["Fred",337,"South America"]
["Fred",284,"North America"]