Tentative Class Outline (will change):

1 Introduction
2 What Excel Does: Calculations & Data Analysis
3 Worksheet Formula Models
4 Data Analysis with PivotTable, Power Query, Data Model, Power BI…
5 Date and Time formulas
6 Text formulas
7 SUMIFS, COUNTIFS & Conditional Calculations
Logical Formulas: Comparative Operators, IF, AND, OR and IS functions
8 Lookup functions: XLOOKUP and FILTER, Data Validation
9 Array Formulas
10 LET and LAMBDA functions
Formulas to Build Reports
11 Visualizing Data with Conditional Formatting, Excel Charts and Power BI
12 Power BI Date and Time Dimension Tables: Server Downtime & 911 Call Data Examples
13 Dashboards
14 Power Query, to clean, transform and import data
15 M Code
16 DAX
17 Much more
Microsoft 365 Ex
Complete Story
Excel Array Formulas
The Full Story of
Their Power!!

Video 9

How to Be Aweso
Excel Formu
Excel Formu
ft 365 Excel
e Story
Array
mulas Formulas
and
Functions

to Be Awesome with
Excel Formulas
Excel Formulas
Micro
36
Exc
Comp
 36
Exc
Comp
Sto
https://www.youtube.com/us
Microsoft
365
Excel
Complete
 365
Excel
Complete
Story
https://www.youtube.com/user/ExcelIsFun/community
 Power Query Formulas
Excel Worksheet Formulas
Microsoft 365 Ex
Complete Story
Array Formulas
Array Functions
50 Examples

Video 9

How to Be Aweso
Excel Formu
Excel Formu
ft 365 Excel
e Story
ulas
tions
s

to Be Awesome with
Excel Formulas
Excel Formulas
Micro
36
Exc
Comp
 36
Exc
Comp
Sto
https://www.youtube.com/us
Microsoft
365
Excel
Complete
 365
Excel
Complete
Story
https://www.youtube.com/user/ExcelIsFun/community
 Power Query Formulas
Excel Worksheet Formulas
 MECS Video #9 - Array Formula Notes

No. Link to Sheet: Topics:

1 AFNotes(1) Array Formula Terms, Definition, and Types
2 ArrayConfig(1) Array Formula Configurations
3 Fun Fundamentals of Array Formulas
4 MakingThingsEasy Array Formulas Make Things Easy
5 CompactSolutions Array Formulas Help Make Compact Solutions
6 SUMPRODUCT SUMPRODUCT function
7 ArrayFunctions List of Array Functions
8 Array Function Examples:

Re-Orient Data. Function examples: CHOOSECOLS, CHOOSEROWS,

DROP, TAKE, VSTACK, HSTACK, SORT, TOCOL, TOROW, TRANSPOSE,
9 Re-Orient WRAPCOLS, WRAPROWS, SORTBY, UNIQUE, EXPAND, FILTER
Create Arrays of Numbers. Function examples: RANDARRAY,
10 Create No. Array SEQUENCE
Statistics & Matrix Algebra. Function examples: FREQUENCY,
11 Stats LINEST, MODE.MULT, TREND
12 Text Text. Function example: TEXTSPLIT

890002248.xlsx - Topics Page 20 of 124

 Value 1 Value 2 Value 3
What Is a Worksheet Array? 22.1243 68.1045 9.1443
1) An array is a collection of two or more items.
2) Items allowed in a worksheet array are:
1. Numbers
2. Text
3. Boolean Values
4. Formula Errors
5. Empty Cells
3) Types of worksheet arrays:
1. Range reference like: C6:C8
2. Array constant like: {1,2} or {1;2} or {1,"Q3";2,"Q3"}
Curly Brackets house the array: { }
Comma means column ,
Semi-colon means row ;
3. Resultant array like: ROUND(C6:C8*J6,2) in =SUM(ROUND(C6:C8*J6,2)) delivers the resultant array = {94.76;39.89;2416.24}
4. Dynamic Spilled Array like =ROUND(C6:C8*J6,2) spills into worksheet: 94.76 39.89 2416.24
Define Worksheet Array Formula:
1) A worksheet array formula is a formula that contains at least one operation that delivers an array of answers rather than a single answer.
For example, the formula =B3:C3*2 = {10,8}*2 = {20,16} has a math operation that delivers an array of answers and is therefore an array formula.
2) When a formula operation delivers an array of answers, the operation is called an array operation.
Whereas as array operation delivers an array of answers, an aggregate operation operates on an array of items, but delivers a single answer (like SUM function).
3) The array of answers generated by an array operation is called a resultant array.
4) When you create an array formula with an array operation that yields a resultant array,
that resultant array can either be the final answer that Excel spills into the worksheet cells or
it can be used as a formula element within a larger formula.
5) The final answer from an array formula can either be a single value (scalar array formulas) or a range of spilled values (dynamic spilled array formula).
1. When the last operation in the array formula evaluation process is an aggregate operation,
the array formula delivers a single value to the worksheet and is called a scalar array formula.
2. When the last operation in the array formula evaluation process is an array operation,
the array formula delivers a range of spilled values into the worksheet and is called a dynamic spilled array formula.
Array operations that are possible in a worksheet are:
1) Direct array operation with an operator (math, comparative, join) that operates on an array of values, such as:
Math operator: =B3:C3*2 = {10,8}*2 = {10*2,8*2} = {20,16}
Comparison operator: =B3:C3>=2 = {10,8}>=2 = {10>=2,8>=2} = {TRUE,TRUE}
Join operator: =B3:C3&2 = {10,8}&2 = {10&2,8&2} = {“102”,”82”}
2) Function argument array operation, where the argument expects a single value, and you give it an array of values.
A function argument array operation forces the function to deliver an array of answers.
Example: =ROUND(C5:C7*D9,2) = ROUND({249.80445;276.20985;197.89875},2) = {249.8;276.21;197.9}
Example: =XMATCH(H5:H8,F5:F7) = {#N/A;1;2;#N/A}
3) Array function operation (using a function such as UNIQUE, TRANSPOSE, SEQUENCE, or MMULT), which is programmed to deliver an array of answers.
Example: =UNIQUE(B5:B11) = {Quad; Carlota; Aspen}
Example: =TRANSPOSE(E5#) = TRANSPOSE({Quad; Carlota; Aspen}) = {Quad, Carlota, Aspen}
Example: =SEQUENCE(3) = {1;2;3}
Example: =MMULT(B5:D5,C10:D12) = {$1,183.00, $1,157.00}
Array Formula Notes:
1) Not all worksheet functions can spill results. Aggregate functions like SUM, AVERAGE, AND, OR, and SUMPRODUCT cannot deliver spilled arrays.
2) Spilled array formulas are not allowed in Excel Tables.
3) Some function arguments do not allow function argument array operations, such as:
1. Range argument of the functions SUMIF, COUNTIF, and AVERAGEIF
2. Criteria_range argument of the functions SUMIFS, COUNTIFS, AVERAGEIFS, MINIFS, and MAXIFS
3. First argument of the functions SUMIFS, AVERAGEIFS, MINIFS, and MAXIFS C
4. Lookup_value argument in VLOOKUP
5. Lookup_value argument in HLOOKUP
4) Almost all array operations involve operations on multiple formula inputs, such as C5:C7*D9, where a column of values is multiplied by a single value.
The exceptions are array functions like SEQUENCE, RANDARRAY, and MUNIT, which are each programmed to generate
an array of answers from a single input (for example, =SEQUENCE(3) = {1;2;3}).

ArrayNotesOld - ArrayNotesOld Page 21 of 124

 A B C D E F G H I
1
2 1) An array is a collection of two or more items.
3
4 Sale ($) Tax ($) Tax Rate 0.765
5 123.87 94.76
6 52.14 39.89 Add Top 2 ($)
array
7 3,158.49 2,416.24 3,282.36
8
9 Err:509 Err:509
10
11 array
12
13 2) Items allowed in a worksheet array are:
14
15 Items: Add ($)
16 Numbers 43 43
17 Text Excel
18 Boolean Values 1 Err:509
19 Formula Errors #DIV/0!
20 Empty Cells array contains all 5 items
21
3)
22 A worksheet array formula is a formula that contains at least one operation that delivers an array of
answers rather than a single answer.
23
24 Numbers Divide by 2 Divide by List Count Unique:
25 10 5 2 Quad 2
26 8 4 Aspen
27 Quad
28 ###
29 Err:509
30
math operation delivers an array of
answers and therefore this is an UNIQUE function delivers an array of answers into
31 array formula ROWS function and therefore this is an array formula
32
33
34 4) When a formula operation delivers an array of answers, the operation is called an array operation.
35 The array of answers generated by an array operation is called a resultant array.
36 resultant array
37 ###
38
39 math array Err:509
40 operation
array function operation
41
42 5) An aggregate operation operates on an array of items, but delivers a single answer.
43
44 Numbers Add Numbers
45 10 18 Err:509
46 8
SUM aggregate operation delivers a single answer.
47
SUM and all other aggregate functions can not deliver an array of answers.
48 A formula like this is not an array formula.
49

890002248.xlsx - AFNotes(1-old) Page 22 of 124

 A B C D E F G H I
50
51 6) Array constant = vales typed into formula using array syntax.
52
53 Array Syntax:
54 Curly Brackets house the array: { }
55 Comma means column ,
56 Semi-colon means row ;
57
58 Array constant of column values in a row: 1 2 3
59 E58: {={1,2,3}}
60 Array constant of row values in a column: 1
61 E60: {={1;2;3}} 2
62 3
63
64 Array constant of table values: 1 Jan
65 E64: {={1,"Jan";2,"Feb";3,"Mar"}} 2 Feb
66 3 Mar
67
68
69 7) Types of worksheet arrays: Tax Rate Sale ($)
70 1. Range reference 0.765 123.87
71 2. Array constant 52.14
72 3. Resultant array Top Two ($) 3,158.49
73 4. Dynamic spilled array 2416.24 1. range reference
74 94.76 2. array constant
75
76 Err:509
77 Day Fiscal Quarter
78 4/12/2023 Q4 Calculation steps:
4.
79 5/9/2023 Q4 dynamic LARGE( {94.76;39.89;2416.24} ,{1;2})
80 11/13/2023 Q2 spilled {2416.24;94.76}
array 3. resultant array from ROUND
81 2/8/2023 Q3
82 9/8/2023 Q1 3. resultant array from LARGE
83 3. array constant
(full table)
84 ###
85
86
87 8) The final answer from an array formula can either be a range of spilled values or a single value.
88
89 When the last operation in an array formula is an array operation, the formula delivers a range of
spilled values into the worksheet and is called a dynamic spilled array formula.
90
91 Numbers Divide by 2 Divide by
dynamic
92 10 5 spilled 2
93 8 4 array
formula
94
95 Err:509
96
97 When the last operation in an array formula is an aggregate operation, the formula delivers a single
value and is called a scalar array formula.
98 array operation makes it an array formula
99 Total scalar array
formula
100 9 Err:509
101
102 SUM to deliver one answer makes it a scalar array formula
890002248.xlsx - AFNotes(1-old) Page 23 of 124
 A B C D E F G H I
1
2 1) An array is a collection of two or more items.
3
4 Sale ($) Tax ($) Tax Rate 0.765
5 123.87 94.76
6 52.14 39.89 Add Top 2 ($)
array
7 3,158.49 2,416.24 3,282.36
8
9 Err:509 Err:509
10
11 array
12
13 2) Items allowed in a worksheet array are:
14
15 Items: Add ($)
16 Numbers 43 43
17 Text Excel
18 Boolean Values 1 Err:509
19 Formula Errors #DIV/0!
20 Empty Cells array contains all 5 items
21
22
23 3) Array constant = vales typed into formula using array syntax.
24
25 Array Syntax:
26 Curly Brackets house the array: { } ###
27 Comma means column ,
28 Semi-colon means row ;
29
30 Array constant of column values in a row: 1 2 3
31
32 Array constant of row values in a column: 1 ###
33 2 Err:509
34 3
35
36 Array constant of table values: 1 Jan
37 2 Feb
38 3 Mar
39

890002248.xlsx - AFNotes(1-test) Page 24 of 124

 A B C D E F G H I
4)
40 A worksheet array formula is a formula that contains at least one operation that delivers an array of
answers rather than a single answer.
41
42 Numbers Divide by 2 Divide by List Count Unique:
43 10 5 2 Quad 2
44 8 4 Aspen
45 Quad
46 ###
47 Err:509
48
math operation delivers an array of
answers and therefore this is an UNIQUE function delivers an array of answers into
49 array formula ROWS function and therefore this is an array formula
50
51 5) An aggregate operation operates on an array of items, but delivers a single answer.
52
53 Numbers Add Numbers
54 10 18 Err:509
55 8
SUM aggregate operation delivers a single answer.
56
SUM and all other aggregate functions can not deliver an array of answers.
57 A formula like this is NOT an array formula.
58
59
60 6) The final answer from an array formula can either be a range of spilled values or a single value.
61
62 When the last operation in an array formula is an array operation, the formula delivers a range of
spilled values into the worksheet and is called a dynamic spilled array formula.
63
64 Numbers Divide by 2 dynamic Divide by
65 10 5 spilled 2
66 8 4 array
formula
67
68 Err:509
69
70 When the last operation in an array formula is an aggregate operation, the formula delivers a single
value and is called a scalar array formula.
71 array operation makes it an array formula
72 Total scalar array
formula
73 9 Err:509
74
75
SUM to deliver one answer makes it a scalar array formula
76
77 7) When a formula operation delivers an array of answers, the operation is called an array operation.
78 The array of answers generated by an array operation is called a resultant array.
79 resultant array
80 ###
81
82 math array Err:509
83 operation
array function operation
84

890002248.xlsx - AFNotes(1-test) Page 25 of 124

 A B C D E F G H I
85
86 8) Types of worksheet arrays: Tax Rate Sale ($)
87 1. Range reference 0.765 123.87
88 2. Array constant 52.14
89 3. Resultant array Top Two ($) 3,158.49
90 4. Dynamic spilled array 2416.24 1. range reference
91 94.76 2. array constant
92
93 Err:509
94 Day Fiscal Quarter
95 4/12/2023 Q4 Calculation steps:
4.
96 5/9/2023 Q4 dynamic LARGE( {94.76;39.89;2416.24} ,{1;2})
97 11/13/2023 Q2 spilled {2416.24;94.76}
array 3. resultant array from ROUND
98 2/8/2023 Q3
99 9/8/2023 Q1 3. resultant array from LARGE
100 3. array constant
(full table)
101 ###
102

890002248.xlsx - AFNotes(1-test) Page 26 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F G H I

1 Array Formula Terms, Definition, and Types by excelisfun

2
3 1) An array is a collection of two or more items.
array
4
5 Sale ($) Tax ($)
6 123.87 94.76 Err:509
7 52.14 39.89
8 3,158.49 2,416.24
array
9
10 Tax Rate Add Top 2 ($)
11 0.765 3,282.36 Err:509
12
13
14
15 2) Items allowed in a worksheet array are:
16
17 Items: Add ($)
18 Numbers: 43 43 ###
19 Text: Excel
20 Boolean: 1 array contains all 5 items
21 Errors: #DIV/0!
22 Empty Cells:
23
24
25 3) Array constant = vales typed into formula (hard coded in) using array syntax.
26
27 Array Syntax:
28 Curly Brackets house the array: { } ###
29 Comma means column ,
30 Semi-colon means row ;
31
32 Array constant of column values in a row: 1 2 3
33
34 Array constant of row values in a column: 1 ###
35 2 Err:509
36 3
37
38 Array constant of table values: 1 Jan
39 2 Feb
40 3 Mar
41

890002248.xlsx - AFNotes(1) Page 27 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F G H I
42
4)
43 A worksheet array formula is a formula that contains at least one operation that delivers an array of
answers rather than a single answer.
44 Divide by 2
45 Numbers Divide by 2
46 10 5 Err:509
47 8 4
math operation delivers an array of answers and
48 therefore this is an array formula
49 List Count Unique:
50 Quad 2 Err:509
51 Aspen
UNIQUE function delivers an array of answers into
52 Quad ROWS function and therefore this is an array formula
53
54
55 5) An aggregate operation operates on an array of items, but delivers a single answer.
56
57 Numbers Add Numbers
NOT an array
58 10 18 Err:509 formula!!
59 8
60 The SUM aggregate operation delivers a single answer.
61 SUM and all other aggregate functions can not deliver an array of answers.
62
63
64 6) The final answer from an array formula can either be a range of spilled values or a single value.
65
66 When the last operation in an array formula is an array operation, the formula delivers a range of
spilled values into the worksheet and is called a dynamic spilled array formula.
67
68 Numbers Divide by 2 Divide by 2
69 10 5 Err:509
70 8 4
71
dynamic spilled array formula
72

73 When the last operation in an array formula is an aggregate operation, the formula delivers a single
value and is called a scalar array formula.
74 array operation makes it an array formula
75 Total
76 9 Err:509
77
scalar array formula SUM to deliver one answer makes it a scalar array formula
78
79
80 7) When a formula operation delivers an array of answers, the operation is called an array operation.
81 The array of answers generated by an array operation is called a resultant array.
82 resultant array
83 ###
84
85 math array operation Err:509

890002248.xlsx - AFNotes(1) Page 28 of 124

 MECS Video #9 - Array Formula Notes

A B array operation
math C D E F G H I
86
array function operation
87

890002248.xlsx - AFNotes(1) Page 29 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F G H I
88
89 8) Types of worksheet arrays:
90 1. Range reference
91 2. Array constant
92 3. Resultant array
93 4. Dynamic spilled array
94
95 Sale ($) Tax Rate
96 123.87 0.765 1. range reference 2. array constant
97 52.14
98 3,158.49 Top Two ($)
99 2416.24 Err:509
100 94.76
101 Calculation steps:
102 LARGE( {94.76;39.89;2416.24} ,{1;2})
103 3. resultant array from LARGE {2416.24;94.76}
104
3. resultant array from ROUND
105
106
107 Day Fiscal Quarter
108 4/12/2023 Q4
109 5/9/2023 Q4
110 11/13/2023 Q2
111 2/8/2023 Q3 3. array constant (full table)
112 9/8/2023 Q1
113
4. dynamic
114 spilled array Err:509
115

890002248.xlsx - AFNotes(1) Page 30 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F G H I
1
2 9) Array operations that are possible in a worksheet are:
3
4 1] Direct array operation: math, comparative, join operator works directly against an array of values,
such as:
5
6 Quad Cost Aspen Cost
7 10 8
8
9 Quad Cost*2 Aspen Cost*2 Quad Cost>=2 Aspen Cost>=2 Quad Cost&2 Aspen Cost&2
10 20 16 1 1 102 82
11
12
13 Math: Comparative: Join:
14 Err:509 Err:509 Err:509
15 Calculation steps: Calculation steps: Calculation steps:
16 {10,8}*2 {10,8}>=2 {10,8}&2
17 {10*2,8*2} {10>=2,8>=2} {10&2,8&2}
18 {20,16} {TRUE,TRUE} {“102”,”82”}
19

2] Function argument array operation: function argument expects a single value, and you give it an
20 array of values. A function argument array operation forces the function to deliver an array of
answers, such as:
21
22 Tax Rate 10.5%
23
24 Employee Gross Pay ($) Tax ($) ROUND:
25 Chantel 2,379.09 249.8 Err:509
26 Kamala 2,630.57 276.21 Calculation steps:
27 Sioux 1,884.75 197.9 ROUND( {249.804;276.209;197.898} ,2)
28 {249.80;276.21;197.90}
29 Item in List 2,
30 List 1 List 2 also in List 1? XMATCH:
31 Gigi Chantel #NAME? Err:509
32 Ty Gigi #NAME? Calculation steps:
33 Sioux Ty #NAME? {#N/A;1;2;#N/A}
34 Miki #NAME?
35

36 3] Array function operation: functions such as UNIQUE and are programmed to deliver an array of
answers.
37
38 No. Products Unique List: UNIQUE:
39 #NAME? Quad #NAME? Err:509
40 #NAME? Carlota #NAME? Calculation steps:
41 #NAME? Quad {"Quad";"Carlota"}
42 #NAME? Quad
43
44 SEQUENCE:
45 Err:509
46 Calculation steps:

890002248.xlsx - AFNotes(2) Page 31 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F G H I
47 {1;2;3;4}
48

890002248.xlsx - AFNotes(2) Page 32 of 124

 MECS Video #9 - Array Formula Notes

10) Dynamic Spilled Array Formula Notes:

When you create a dynamic spilled array formula:
1. The formula lives in the top cell.
2. Spilled values spill down and to the right.
3. To edit a dynamic spilled array formula, you edit the formula in the top left cell.
4. Cells below the top cell do not contain values. All values emanate from the top cell.
5. Even though the values below the top cell do not live in the cell, you can refer to a value in
any of the spilled range with a cell reference.
6. If a cell value is in the path of the spilled values you will get a #SPILL! error
7. You refer to a spilled range of values using the top cell address and the spilled range
operator: # symbol, like E5#

8. The most amazing characteristic of dynamic spilled array formulas is that when the source
data changes and the resultant array expands (or contracts), the spilled range dynamically
updates.
9. Not all worksheet functions can spill results. Aggregate functions like SUM, AVERAGE, AND,
OR, and SUMPRODUCT cannot deliver spilled arrays.
10. Spilled array formulas are not allowed in Excel Tables.

11. Some function arguments do not allow function argument array operations, such as:
* Range argument of the functions SUMIF, COUNTIF, and AVERAGEIF.
* Criteria_range argument of the functions SUMIFS, COUNTIFS, AVERAGEIFS, MINIFS, &
MAXIFS.
* First argument of the functions SUMIFS, AVERAGEIFS, MINIFS, and MAXIFS COUNTIFS.
* Lookup_value argument in VLOOKUP and HLOOKUP.

12. Almost all array operations involve operations on multiple formula inputs, such as
C5:C7*D9, where a column of values is multiplied by a single value. The exceptions are array
functions like SEQUENCE, RANDARRAY, and MUNIT, which are each programmed to
generate an array of answers from a single input (for example, =SEQUENCE(3) = {1;2;3}).

890002248.xlsx - AFNotes(3) Page 33 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F G
1
2 11) Array Operation Configurations, the Evaluation Process, and the Size of the Resultant Array
3
4 Multiple 2
5
6 Sales Tax ($) Cell * Column:
7 10 20 Err:509
8 20 40 #NAME?
9 =Cell (1R x 1C) * Column (2R x 1C) = Column (2R x 1C)
10
11 Price ($) Units Sales Column * Column (same number of rows):
12 2 5 10 Err:509
13 5 4 20 #NAME?
14 =Column (2R x 1C) * Column (2R x 1C) = Column (2R x 1C)
15
16 Price ($) Units Sales Column * Column (different number of rows):
17 2 5 10 Err:509
18 5 4 20 ={2;5}*{5;4;2} = {2*5;5*4;???*4} = {10;20;#N/A}
19 =Column (2R x 1C) * Column (3R x 1C) = Error because columns do not
2 #N/A contain same number of rows.
20
21 Sales 10 20 Cell * Row:
22 Tax Paid 20 40 Err:509
23 #NAME?
24 Multiple 2 =Cell (1R x 1C) * Row (1R x 2C) = Row (1R x 2C)
25
26 Price ($) 2 5 Row * Row (same number of columns):
27 Units 5 4 Err:509
28 Sales 10 20 #NAME?
29 =Row (1R x 2C) * Row (1R x 2C) = Row (1R x 2C)
30
31 Price ($) 2 5 Row * Row (different number of columns):
32 Units 5 4 2 Err:509
33 Sales 10 20 #N/A ={2,5}*{5,4,2} = {2*5;5*4;???*4} = {10,20,#N/A}
34 =Row (1R x 2C) * Row (1R x 3C) = Error because rows do not contain same
number of columns
35

890002248.xlsx - ArrayConfig(1) Page 34 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F G
1
2 10 20 Array Formula Process for Column * Row:
3 2 20 40 Err:509
4 1 10 20 ={2;1;3}*{10,20} = {2*10,2*20;1*10,1*20;3*10,3*20} = {20,40;10,20;30,60}
5 3 30 60 =Column (3R x 1C) * Row (1R x 2C) = Table (3R x 2C)
6
7 2 1 3 Array Formula Process for Row * Column:
8 10 20 10 30 Err:509
9 20 40 20 60 ={2,1,3}*{10;20} = {2*10,1*10,3*10;2*20,1*20,3*20} = {20,10,30;40,20,60}
10 =Column (3R x 1C) * Row (1R x 2C) = Table (3R x 2C)
11
12 14 10 Array Formula Process for Table * Table:
13 4 15 Err:509
14 3 24 ={2,5;4,3;1,6}*{7,2;1,5;3,4} = {2*7,5*2;4*1,3*5;1*3,6*4} = {14,10;4,15;3,24}
15 Table (3R x 2C) * Table (3R x 2C) = Table (3R x 2C)
16 2 5
17 4 3
18 1 6
19
20 7 2
21 1 5
22 3 4
23
24 1 12 Array Formula Process for Column * Row * Table:
25 10 8 Err:509
26 ={1;2;1}*{1,4}*{1,3;5,1;1,2} = {1*1,1*4;2*1,2*4;1*1,1*4}*{1,3;5,1;1,2} =
1 8 {1,4;2,8;1,4}*{1,3;5,1;1,2} = {1*1,4*3;2*5,8*1;1*1,4*2] = {1,12;10,8;1,8}
27 =Column (3R x 1C) * Row (1R x 2C) * Table (3R x 2C) = Table (3R x 2C)
28
Row è
29 Column
ê 1 4
30 1 1 3
31 2 5 1
32 1 1 2
33

890002248.xlsx - ArrayConfig(2) Page 35 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F
1
2 Total Sales
3 Product/SalesRep #NAME? #NAME? Function Argument Array Operation in SUMIFS
4 #NAME? #NAME? #NAME? Err:509
5 #NAME? #NAME? #NAME? =SUMIFS(D3:D8,B3:B8, {"Aspen";"Quad"} ,C3:C8, {"Sioux","Ty"} )
6 Two function argument array operations:
7 Product SalesRep Sales {"Aspen";"Quad"} = Column (2R x 1C)
8 Quad Sioux 10 {"Sioux","Ty"} = Row (1R x 2C)
9 Aspen Ty 20 SUMIFS delivers a cross tabulated table (2R x 2C) like this:
10 Quad Ty 20 #NAME?
11 Aspen Ty 20
12 Aspen Sioux 20
13 Quad Sioux 10
14
15 Contains Criteria Contains? Function Argument Array Operation in SEARCH
16 Carlota #VALUE! Err:509
17 Quad #VALUE! =SEARCH( {"Carlota";"Quad"} ,B20:B25)
18 #VALUE! Function argument array operation:
19 Product #VALUE! {"Carlota";"Quad"} = Column (2R x 1C),
20 Quad #VALUE! Because B20:B25 is 6 rows and {"Carlota";"Quad"} is 2 rows,
21 Aspen #VALUE! SEARCH delivers a column of errors:
22 Quad {#VALUE!;#VALUE!;#N/A;#N/A;#N/A;#N/A}
23 Aspen When more than one row is involved in array operation,
24 Aspen they must have same number of rows.
25 Quad When they do not, we get an error.
26

890002248.xlsx - ArrayConfig(3) Page 36 of 124

 Array Formula Quick Notes

Types of Operations =
Direct
Math
Join
Logical
Function argument
Function
Resultant Array = array created by array operation
Types of Arrays =
Range
Resultant Array
Dynamic Spilled Array
Array Constant: {} House Array. , = Column. ; = Row.
Define Worksheet Array Formula = formula that contains at least one operation that deliver an array of answers,
rather than a single answer
Types of Array Formulas =
Dynamic Spilled Array Formulas
Scalar Array Formula
Dynamic Array Formula Characteristics:
1. Dynamic spilled array formula lives in top cell
2. Formula spills down from and to right of the top cell
3. Edit formula in top cell
4. Cells below top cell do not contains values, but can be referenced
5. If cell content is in way of spilled results, you get a #SPILL! error
6. Spilled Range Operator is: #. Refer to dynamic spilled array with top cell and #, like: I5#
7. Spilled arrays not allowed in Excel Tables
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L M N O P Q
1 Commission Rate
2 0.0575
3
4 Date Employee Sales($) Commission $ Total Sales Unique Dates Employee Total Sales 2 Biggest Sales Sales Rep?
5 10/26/2022 Chantel Mimms 1,211.71 69.67 77,500.29 #NAME? #NAME? #NAME? 14,338.83 #NAME?
6 10/26/2022 Nanjala Gwenevere 1,269.93 73.02 #NAME? #NAME? #NAME? 12,990.78 #NAME?
7 10/26/2022 Sioux Radcoolinator 879.40 50.57 #NAME? #NAME? #NAME?
8 10/26/2022 Timmy Smith 957.74 55.07 Total Comm$ #NAME? #NAME? #NAME?
9 10/27/2022 Chantel Mimms 1,212.54 69.72 4,456.26 #NAME? #NAME? #NAME?
10 10/27/2022 Nanjala Gwenevere 652.94 37.54
11 10/27/2022 Sioux Radcoolinator 891.45 51.26
12 10/27/2022 Timmy Smith 128.30 7.38 Err:509
13 10/28/2022 Chantel Mimms 890.25 51.19 Err:509
14 10/28/2022 Nanjala Gwenevere 719.90 41.39 Err:509
15 10/28/2022 Sioux Radcoolinator 724.66 41.67 Err:509
16 10/28/2022 Timmy Smith 1,213.85 69.8 Err:509
17 10/29/2022 Chantel Mimms 11,222.47 645.29 Err:509
18 10/29/2022 Nanjala Gwenevere 12,990.78 746.97 Err:509
19 10/29/2022 Sioux Radcoolinator 2,957.70 170.07 Err:509
20 10/29/2022 Abigail Sinclaire 3,786.09 217.7 Err:509
21 10/30/2022 Chantel Mimms 5,516.06 317.17
22 10/30/2022 Nanjala Gwenevere 5,047.96 290.26 Join:
23 10/30/2022 Sioux Radcoolinator 14,338.83 824.48 Chantel Mimms comm = 69.67
24 10/30/2022 Abigail Sinclaire 10,887.73 626.04 Nanjala Gwenevere comm = 73.02 Array = 2 or more items
25 Sioux Radcoolinator comm = 50.57 Aggregate Operation = operates on an array of items, but delivers a single answer
26 Timmy Smith comm = 55.07 Array Operation = formula operation that delivers an array of answers
27 Chantel Mimms comm = 69.72 Types of Operations =
28 Nanjala Gwenevere comm = 37.54 Direct
29 Sioux Radcoolinator comm = 51.26 Math
30 Timmy Smith comm = 7.38 Join
31 Chantel Mimms comm = 51.19 Logical
32 Nanjala Gwenevere comm = 41.39 Function argument
33 Sioux Radcoolinator comm = 41.67 Function
34 New Records: Timmy Smith comm = 69.8 Resultant Array = array created by array operation
35 Chantel Mimms comm = 645.29 Types of Arrays =
36 10/29/2022 Chantel Mimms 11222.47 Nanjala Gwenevere comm = 746.97 Range
37 10/29/2022 Nanjala Gwenevere 12990.78 Sioux Radcoolinator comm = 170.07 Resultant Array
38 10/29/2022 Sioux Radcoolinator 2957.7 Abigail Sinclaire comm = 217.7 Dynamic Spilled Array
39 10/29/2022 Abigail Sinclaire 3786.09 Chantel Mimms comm = 317.17 Array Constant: {} House Array. , = Column. ; = Row.
40 10/30/2022 Chantel Mimms 5516.06 Nanjala Gwenevere comm = 290.26 Define Worksheet Array Formula = formula that contains at least one operation that deliver an array of answers,
41 10/30/2022 Nanjala Gwenevere 5047.96 Sioux Radcoolinator comm = 824.48 rather than a single answer
42 10/30/2022 Sioux Radcoolinator 14338.83 Abigail Sinclaire comm = 626.04 Types of Array Formulas =
43 10/30/2022 Abigail Sinclaire 10887.73 Dynamic Spilled Array Formulas
44 Hurdle: Scalar Array Formula
45 60 Dynamic Array Formula Characteristics:
46 1. Dynamic spilled array formula lives in top cell
47 Boolean: 2. Formula spills down from and to right of the top cell
48 1 3. Edit formula in top cell
49 1 4. Cells below top cell do not contains values, but can be referenced
50 0 5. If cell content is in way of spilled results, you get a #SPILL! error
51 0 6. Spilled Range Operator is: #. Refer to dynamic spilled array with top cell and #, like: I5#
52 1 7. Spilled arrays not allowed in Excel Tables
53 0

890002248.xlsx - Fun (an) Page 38 of 124

Why Array Formulas are useful:
1) Make Worksheet Formulas Easier Than Before with Dynamic Spilled Array Formulas
Dynamic Spilled Array Formulas:
Don't have to lock cell references
Don't have to manually copy formula
Editing is done in top cell with no re-copy

Goal 1: Create Profit Budget

Budget Income
Statement by Month Jan Feb Mar
Expenses as % of Sales % Revenue 5,000 4,500 4,375
COGS Expense 0.375 COGS Expense
Research Expense 0.125 Research Expense
Selling & Marketing Ex. 0.0975 Selling & Marketing Ex.
Interest Expense 0.05 Interest Expense
Income Tax Expense 0.175 Income Tax Expense
Other Expense 0.015 Other Expense
Total Expenses 0 0 0
Net Income (Profit)

Err:509

Goal 2: Create Schedule of Future Value Retirement Amounts

Retirement Assumptions: Year/Rate of Return

Start Years to invest 10
Number Year Examples 8
Year Increment 5
Start Rate of Return 2.0%
Number Rate Examples 6
Rate Increment 2.0%
Yearly Deposit ($) 10,000
Initial Deposit ($) 5,000

Err:509
FV = Year/Rate of Return #NAME? #NAME? #NAME?
PMT*[((1+r)^x-1)/r]+PV*(1+r)^x #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
Apr May Jun Jul Aug Sep Oct Nov
8,540 9,700 1,452 1,500 1,125 9,050 1,375 1,950

0 0 0 0 0 0 0 0
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
Dec Total
2,275 50,842
0
0
0
0
0
0
0 0
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L M N O P Q R
1
2 Why Array Formulas are useful:
3 1) Make Worksheet Formulas Easier Than Before with Dynamic Spilled Array Formulas
4 Dynamic Spilled Array Formulas:
5 Don't have to lock cell references
6 Don't have to manually copy formula
7 Editing is done in top cell with no re-copy
8
9 Goal 1: Create Profit Budget
10 Budget Income
Statement by Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
11 Expenses as % of Sales % Revenue 5,000 4,500 4,375 8,540 9,700 1,452 1,500 1,125 9,050 1,375 1,950 2,275 50,842
12 COGS Expense 0.375 COGS Expense 1,875 1,688 1,641 3,203 3,638 545 563 422 3,394 516 731 853 19,066
13 Research Expense 0.125 Research Expense 625 563 547 1,068 1,213 182 188 141 1,131 172 244 284 6,355
14 Selling & Marketing Ex. 0.0975 Selling & Marketing Ex. 488 439 427 833 946 142 146 110 882 134 190 222 4,957
15 Interest Expense 0.05 Interest Expense 250 225 219 427 485 73 75 56 453 69 98 114 2,542
16 Income Tax Expense 0.175 Income Tax Expense 875 788 766 1,495 1,698 254 263 197 1,584 241 341 398 8,897
17 Other Expense 0.015 Other Expense 75 68 66 128 146 22 23 17 136 21 29 34 763
18 Total Expenses 4,188 3,769 3,664 7,152 8,124 1,216 1,256 942 7,579 1,152 1,633 1,905 42,580
19 Net Income (Profit) 813 731 711 1,388 1,576 236 244 183 1,471 223 317 370 8,262
20
21 Err:509
22
23 Goal 2: Create Schedule of Future Value Retirement Amounts
24 Single Cell Solution from Y
25 Retirement Assumptions: Year/Rate of Return #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
26 Start Years to invest 10 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? ### #NAME?
27 Number Year Examples 8 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? ### #NAME?
28 Year Increment 5 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? ### #NAME?
29 Start Rate of Return 2.0% #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? ### #NAME?
30 Number Rate Examples 6 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? ### #NAME?
31 Rate Increment 2.0% #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? ### #NAME?
32 Yearly Deposit ($) 10,000 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? ### #NAME?
33 Initial Deposit ($) 5,000 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? ### #NAME?
34 ### #NAME?
35 Err:509
36

890002248.xlsx - MakingThingsEasy (an) Page 44 of 124

Why Array Formulas are useful:
2) Help Build More Compact Solutions
Scarlar Array Formulas can eliminate unnecessay intermediate steps in the worksheet

Goal 1: Estimate returns for stock A & B in a portfolio of stocks.

Weight of Stock in
Portfolio: 60.00% 40.00%
Probability of Stock A Contribution
Economic State Stock A Estimated Return Stock B Estimated Return to Portfolio
Bad 35.00% 2.50% -7.50%
OK 25.00% 8.50% 12.50%
Good 40.00% 14.50% 22.00%
Estimated Portfolio Estimated Portfolio
Returns: Returns:

Err:509

Goal 2: Estimate the Overall Profit for Next Year

Net Year Estimated

Expenses as % of Sales % Profit:
COGS Expense 0.375
Research Expense 0.125
Selling & Marketing Ex. 0.0975
Interest Expense 0.05
Income Tax Expense 0.175
Other Expense 0.015
Revenue $
Jan 5,000
Feb 4,500
Mar 4,375
Apr 8,540 Err:509
May 9,700
Jun 1,452
Jul 1,500
Aug 1,125
Sep 9,050
Oct 1,375
Nov 1,950
Dec 2,275
Stock B Contribution
to Portfolio
Err:509

Err:509
Err:509
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L
1
2 Why Array Formulas are useful:
3 2) Help Build More Compact Solutions
4 Scarlar Array Formulas can eliminate unnecessay intermediate steps in the worksheet
5
6
7 Goal 1: Estimate returns for stock A & B in a portfolio of stocks.
8
9 Weight of Stock in
Portfolio: 60.00% 40.00%

10 Probability of Stock A Contribution Stock B Contribution

Economic State Stock A Estimated Return Stock B Estimated Return to Portfolio to Portfolio
11 Bad 35.00% 2.50% -7.50% 0.00525 -0.0105 Err:509
12 OK 25.00% 8.50% 12.50% 0.01275 0.0125
13 Good 40.00% 14.50% 22.00% 0.0348 0.0352
14 Estimated Portfolio Estimated Portfolio
Returns: 9.00% Returns: #NAME? Err:509
15 Err:509
Example of More Compact Solution that
16 avoids many intermedaite worksheet cell Err:509
17 steps
18 Goal 2: Estimate the Overall Profit for Next Year
19

20 Net Year Estimated

Expenses as % of Sales % Profit:
21 COGS Expense 0.375 #NAME?
22 Research Expense 0.125
23 Selling & Marketing Ex. 0.0975 Err:509
24 Interest Expense 0.05
Example of More Compact Solution that
25 Income Tax Expense 0.175 avoids many intermedaite worksheet cell
26 Other Expense 0.015 steps
27 Revenue $
28 Jan 5,000
29 Feb 4,500
30 Mar 4,375
31 Apr 8,540
32 May 9,700
33 Jun 1,452
34 Jul 1,500
35 Aug 1,125
36 Sep 9,050
37 Oct 1,375
38 Nov 1,950
39 Dec 2,275
890002248.xlsx - CompactSolutions (an) Page 47 of 124
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L M N O P Q
1
2 Goal: Calculate Standard deviation for the portfolio that contains stock A & B.
3

4 Weight of Stock in
Portfolio: 0.6 0.4
Stock A Full Stock B Full
5 Probability of Estimated Estimated 3. Subtract 4) * Prob of
Economic State Return Return 1) Weight * Return 2) Add for Econ State Mean & Square Econ
6 Bad 0.5 0.0 -0.15 0 -0.06 -0.06 0.004096 #NAME?
7 OK 0.4 0.05 0.05 0.03 0.02 0.05 0.002116 #NAME?
8 Great 0.1 0.1 0.2 0.06 0.08 0.14 0.018496 #NAME?
9
10 5) Add = Var #NAME?
11 6) SQRT = SD #NAME?
12 Expected Portfolio Returns: 0.0040
13
14 Example of More Compact Solution that avoids all worksheet cell steps that are listed above:
15
16 Standard Deviation of Ex. Portfolio Returns: #NAME?
17 Standard Deviation of Ex. Portfolio Returns: #NAME?
18
19 Err:509
20 Err:509
21

890002248.xlsx - CS(2) Page 48 of 124

 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L M
1
2 Question:
3 Executive at HMO wanted to check Patient Record Data accuracy. An independent and random sample of medical records was taken for each of the four years.
4
5 Year SurveyAnswer Count Year
6 2011 Yes SurveyAnswer 2011 2012 2013 2014 Total Result
7 2012 Yes Yes 39 43 45 41 168
8 2013 No No 304 223 271 341 1139
9 2014 No Total Result 343 266 316 382 1307
10 2011 No
11 2012 No % Column Total Year
12 2013 No SurveyAnswer 2011 2012 2013 2014 Total Result
13 2014 No Yes 11.37% 16.17% 14.24% 10.73% 12.85%
14 2011 No No 88.63% 83.83% 85.76% 89.27% 87.15%
15 2012 No Total Result 100.00% 100.00% 100.00% 100.00% 100.00%
16 2013 No
17 2014 No Expected Value Year
18 2011 No SurveyAnswer 2011 2012 2013 2014 Grand Total
19 2012 No Yes 44.08875 34.19128 40.61821 49.10176 168
20 2013 No No 298.9112 231.8087 275.3818 332.8982 1139
21 2014 No Grand Total 343 266 316 382 1307
22 2011 Yes
23 2012 No Deviation^2 Year
24 2013 No SurveyAnswer 2011 2012 2013 2014
25 2014 No Yes #NAME? #NAME? #NAME? #NAME?
26 2011 Yes No #NAME? #NAME? #NAME? #NAME?
27 2012 No
28 2013 Yes Deviations Squared/Expected Frequencies:
29 2014 No SurveyAnswer 2011 2012 2013 2014
30 2011 No Yes #NAME? #NAME? #NAME? #NAME?
31 2012 No No #NAME? #NAME? #NAME? #NAME?
32 2013 No
33 2014 No Test Statistic Chi-Square = c2 = #NAME?
34 2011 No
35 2012 No Example of More Compact Solution that avoids all worksheet cell steps that are listed above:
36 2013 No
37 2014 No Test Statistic Chi-Square = c2 = 5.354484 Err:509
38 2011 Yes
890002248.xlsx - CS(3) Page 49 of 124
SUMPRODUCT function is an aggregate function that is programmed to make array calculations, but deliver an aggregate a
SUMPRODUCT can multiple 2 or more same size arrays, then add the resultant result to get an aggregate sum total
Arguments: SUMPRODUCT(array1,[array2] … )

Goal 1: Calculate total sales (price * units) in single cell. Time in milliseconds:

Product Price Units Total Sales ($) SUM Times:

c 49.05 6 7.3
q 13.31 126 7.3
a 26.79 1 Total Sales ($) 7.5
c 37.92 167 7.4
q 49.92 78 Ave: 7.38
a 40.37 87 Err:509
c 25.83 32 Err:509 % Decrease:
q 44.77 105
a 18.52 66
c 46.21 163
q 10.7 142
a 30.82 141
c 36.35 31 Goal 2: Multiply each test score by each test weight and then add to get student total sco
q 44.79 137 Example: Sioux's Total Score = 91*20% + 94*25.00% + 91*15.00% + 87.5*40.00% = 18.2 +
a 16.24 21
c 10.64 39 Weights: 20% 25% 15% 40%
q 35.68 147
a 38.54 157 Name Test 1 Test 2 Test 3 Test 4 Weighted Ave.
c 13.18 78 Sioux 91 94 91 87.5
q 30.97 32 Chin 98 87.5 79 86 87.73
a 46.33 109 Ty 73 36 56 78 63.20
c 36.31 4 Mo 65 70 72 84 74.90
q 47.16 1
a 47.95 40 Test Weights Err:509
c 42.54 146 Test 1 20% Err:509
q 26.3 186 Test 2 25%
a 43.08 116 Test 3 15%
c 47.27 68 Test 4 40%
q 40.24 89
c 46.75 10
t deliver an aggregate answer.
ggregate sum total

n milliseconds:

SUMPRODUCT times:
6.6
6.5
6.6
6.6
6.57

-10.85%

d to get student total score.

% + 87.5*40.00% = 18.2 + 23.5 + 13.65 + 35 = 90.35

Weighted Ave. Weighted Ave. Weighted Ave.

90.35 90.35
#NAME? 87.73 87.725
#NAME? 63.20 63.2
#NAME? 74.90 74.9
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L M N
1
2 SUMPRODUCT function is an aggregate function that is programmed to make array calculations, but deliver an aggregate answer.
3 SUMPRODUCT can multiple 2 or more same size arrays, then add the resultant result to get an aggregate sum total
4 Arguments: SUMPRODUCT(array1,[array2] … )
5
6 Goal 1: Calculate total sales (price * units) in single cell. Time in milliseconds:
7
8 Product Price Units Total Sales ($) SUM Times: SUMPRODUCT times:
9 c 49.05 6 284,443,532.22 7.3 6.6
10 q 13.31 126 7.3 6.5
11 a 26.79 1 Total Sales ($) 7.5 6.6
12 c 37.92 167 284,443,532.22 7.4 6.6
13 q 49.92 78 Ave: 7.38 6.57
14 a 40.37 87 Err:509
15 c 25.83 32 Err:509 % Decrease: -10.85%
16 q 44.77 105
17 a 18.52 66
18 c 46.21 163 Goal 2: Multiply each test score by each test weight and then add to get student total score.
19 q 10.7 142 Example: Sioux's Total Score = 91*20% + 94*25.00% + 91*15.00% + 87.5*40.00% = 18.2 + 23.5 + 13.65 + 35 = 90.35
20 a 30.82 141
21 c 36.35 31 Weights: 20% 25% 15% 40%
22 q 44.79 137
23 a 16.24 21 Name Test 1 Test 2 Test 3 Test 4 Weighted Ave. Weighted Ave. Weighted Ave. Weighted Ave.
24 c 10.64 39 Sioux 91 94 91 87.5 90.35 90.35 90.35 90.35
25 q 35.68 147 Chin 98 87.5 79 86 87.73 87.73 87.73 87.725
26 a 38.54 157 Ty 73 36 56 78 63.20 63.20 63.20 63.2
27 c 13.18 78 Mo 65 70 72 84 74.90 74.90 74.90 74.9
28 q 30.97 32
29 a 46.33 109 Test Weights Err:509
30 c 36.31 4 Test 1 20% Err:509
31 q 47.16 1 Test 2 25% Err:509
32 a 47.95 40 Test 3 15% Err:509
33 c 42.54 146 Test 4 40%
34 q 26.3 186
100560 c 46.75 10

890002248.xlsx - SUMPRODUCT (an) Page 52 of 124

 MECS Video #9 - Array Formula Notes

No. Array Function Description Arguments

Re-Orient Data Functions:
Returns the specified column/s from an array with column
1 CHOOSECOLS CHOOSECOLS(array,col_num1,[col_num2],…)
position numbers.
Returns the specified row/s from an array with row position
2 CHOOSEROWS CHOOSEROWS(array,row_num1,[row_num2],…)
numbers.
Drops rows or columns from array start (positive number) or
3 DROP DROP(array, rows,[columns])
end (negative number).
Takes rows or columns from array start (positive number) or
4 TAKE TAKE(array, rows,[columns])
end (negative number).
Appends arrays vertically and in sequence to return a larger
5 VSTACK VSTACK(array1,[array2],...)
array.
Appends arrays horizontally and in sequence to return a larger
6 HSTACK HSTACK(array1,[array2],...)
array.
Returns the array as a single column. Stacks row values by
7 TOCOL TOCOL(array, [ignore], [scan_by_column])
default, unless you use 3rd argument.
Returns the array as a single row. Stacks row values by default,
8 TOROW TOROW(array, [ignore], [scan_by_column])
unless you use 3rd argument.
Wraps the provided row or column (one-dimension) of values
by columns after a specified number of elements to form a
9 WRAPCOLS WRAPCOLS(vector, wrap_count, [pad_with])
new array. Use 3rd argument to pad when there are not
enough values to wrap full array.
Wraps the provided row or column (one-dimension) of values
by rows after a specified number of elements to form a new
10 WRAPROWS WRAPROWS(vector, wrap_count, [pad_with])
array. Use 3rd argument to pad when there are not enough
values to wrap full array.

Expands or pads an array to specified row and column

dimensions, where row or column number must be >= the
11 EXPAND number of rows or columns, respectively, in the original array. EXPAND(array, rows, [columns], [pad_with])
Use 4th argument to pad when there are not enough values to
wrap full array.
12 TRANSPOSE Converts a vertical array into a horizontal array or vice versa. TRANSPOSE(array)
Sorts a row, a column, or a table in ascending or descending
13 SORT order. 2nd argument says which column in table to sort. SORT(array, [sort_index], [sort_order], [by_col])
Default sort order is A-Z.
Sorts an array by the values in one or more corresponding SORTBY(array, by_array, [sort_order],
14 SORTBY
arrays. Default sort order is A-Z. [array/order], ...)
Creates a unique list of values or records. By default it yields a
unique set of row records from a column or table. 2nd
15 UNIQUE argument allows you to get a unique list by columns. 3rd UNIQUE(array, [by_col], [exactly_once])
argument allows you to extract a list of items only listed one
time in original list.
Filters a dataset based on conditions and criteria that you
16 FILTER FILTER(array, include, [if_empty])
specify in a Boolean array logical test in the allow argument.

890002248.xlsx - ArrayFunctions Page 53 of 124

 MECS Video #9 - Array Formula Notes

No. Array Function Description Arguments

Create Arrays of Numbers:

Creates an array of random numbers based on a min and max RANDARRAY([rows],[columns],[min],[max],

17 RANDARRAY number using a uniform distribution. If you enter just a row [whole_number])
number, it generates an array of numbers that span from zero
to one, each with 15 digits.
Generates a sequence of numbers in a row, a column, or a
18 SEQUENCE SEQUENCE(rows, [columns], [start], [step])
table, based on a start value and an increment value (step).
Statistics and Matrix Algebra:
Counts how many values are in each category, given the upper
limit for each category, and returns the counts in a vertical
19 FREQUENCY array that is one more row than there are upper limits. The FREQUENCY(data_array, bins_array)
extra row is an extra category that includes any values that
might be greater than the last upper limit.

Calculates predicted exponential growth by using existing data.

GROWTH returns the y-values for a series of new x-values that
you specify by using existing x-values and GROWTH(known_y's, [known_x's],
20 GROWTH
y-values. You can also use the GROWTH worksheet function to [new_x's], [const])
fit an exponential curve
to existing x-values and y-values.

Returns a set of statistics for single or multiple regression,

21 LINEST using the least-squares method for best fitting data to a LINEST(known_y's, [known_x's], [const], [stats])
straight line.
In regression analysis, calculates an exponential curve that fits
22 LOGEST the data and returns an array of values that describes the LOGEST(known_y's, [known_x's], [const], [stats])
curve.

Returns the matrix product of two arrays. Number columns of

first array must equal number of rows in second array. The
23 MMULT MMULT(array1, array2)
resultant array is the number of rows in first array by the
number of columns in the second array.

Calculates the mode for a set of numbers, where the mode is

24 MODE.MULT the number that occurs most frequently. If there are multiple MODE.MULT((number1,[number2],...)
modes, MODE.MULT lists all modes in a vertical array.
25 MUNIT Returns the unit matrix, given a single number. MUNIT(dimension)
Using the least-squares method for best-fitting data to a
straight line, returns an array of y-values, given these formula TREND(known_y's, [known_x's],
26 TREND
inputs: known y-values, known x-values, and an array of x- [new_x's], [const])
values used to estimate the array of y-values.
Text:
Splits text by using column and/or row delimiters. 5th
TEXTSPLIT(text,col_delimiter,[row_delimiter],
27 TEXTSPLIT argument determines case sensitiveness. Use 6th argument to
[ignore_empty], [match_mode], [pad_with])
specify value to show when no delimiters are found.
The FILTERXML function returns specific data from XML
28 FILTERXML FILTERXML(xml, xpath)
content by using the specified xpath.

890002248.xlsx - ArrayFunctions Page 54 of 124

 MECS Video #9 - Array Formula Notes

Lambda Related Functions:

No. LAMBDA Description Arguments
Defines a function value. It is not an array function by itself, but
can be designed to deliver a single value or an array of values.
Can be used with Defined Names to define re-usable LAMBDA([parameter1, parameter2, …,]
LAMBDA
worksheet function, or can be used in the six LAMBDA helper calculation)
functions: BYCOL, BYROW, MAKEARRAY, MAP, SCAN and
REDUCE to create a new function in the worksheet.

No. LAMBDA Helper

Array Function Description Arguments
The function argument in LAMBDA Helper Array Functions can use the LAMBDA function (to define your own calculation) or one of the 16
Eta-Lambda aggregate functions: SUM, PERCENTOF, AVERAGE, MEDIAN, COUNT, COUNTA, MAX, MIN, PRODUCT, ARRAYTOTEXT, CONCAT,
STDEV.S, STDEV.P, VAR.S, VAR.P, MODE.SNGL.
Iterates a function defined by the LAMBDA function over an
29 BYCOL specified array, making a function calculation for each column BYCOL (array, function)
and returning an array of the results.
Iterates a function defined by the LAMBDA function over an
30 BYROW specified array, making a function calculation for each row and BYROW(array, function)
returning an array of the results.
Returns a calculated array of a specified row and column size,
31 MAKEARRAY MAKEARRAY(rows, cols, function)
by applying a function defined by the LAMBDA function.
Iterates a function defined by the LAMBDA function over an
specified set of arrays (1 or more), making a function
32 MAP calculation for each cell in the corresponding arrays and then MAP (array1, lambda_or_array<#>)
returning an array of the results. Last argument can contain
another array to be mapped.
Scans an array by applying a LAMBDA to each value and
33 SCAN returns an array that has each intermediate value. For SCAN (initial_value, array, function)
example, it can create a spilled running total.
LAMBDA Helper
No. Non-Array
Function Description Arguments
Checks whether the value in a LAMBDA is missing and returns
ISOMITTED ISOMITTED(argument)
TRUE or FALSE.
Array Functions
No. that deliver an
aggregate answer Description Arguments
Reduces an array to an accumulated value by applying a
34 REDUCE LAMBDA to each value and returning the last value in the REDUCE([initial_value], array, function)
accumulator array.
SUMPRODUCT function is an aggregate function that is
35 SUMPRODUCT programmed to make array calculations, but deliver an SUMPRODUCT(array1,[array2] … )
aggregate answer.

890002248.xlsx - ArrayFunctions Page 55 of 124

 Quarter 1 Quarter 2 Re-Orient Data
Date Student Test Score Date Student Test Score Functions
1/6/2023 Sioux Test 1 87 3/1/2023 Sioux Test 4 87 CHOOSECOLS
2/10/2023 Sioux Test 2 86 3/15/2023 Sioux Test 5 86 CHOOSEROWS
2/22/2023 Sioux Test 3 70 4/3/2023 Sioux Test 6 70 DROP
1/6/2023 Chantel Test 1 96 3/1/2023 Chantel Test 4 87 TAKE
2/10/2023 Chantel Test 2 90 3/15/2023 Chantel Test 5 90 VSTACK
2/22/2023 Chantel Test 3 90 4/3/2023 Chantel Test 6 90 HSTACK
1/6/2023 Mo Test 1 89 3/1/2023 Mo Test 4 89 SORT
2/10/2023 Mo Test 2 45 3/15/2023 Mo Test 5 90 TOCOL
2/22/2023 Mo Test 3 93 4/3/2023 Mo Test 6 93 TOROW
1/6/2023 Ty Test 1 99 3/1/2023 Ty Test 4 99 TRANSPOSE
2/10/2023 Ty Test 2 92 3/15/2023 Ty Test 5 90 WRAPCOLS
2/22/2023 Ty Test 3 92 4/3/2023 Ty Test 6 92 WRAPROWS
SORTBY
UNIQUE
Top 4 include ties: EXPAND
Top 4 FILTER
Value 93
Err:509
Student ALL Scores
#NAME? #NAME? ###
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
 Prod/SR Joe Sioux Solution from victor at You
Quad 12 24
Aspen 11 14 Pro
Carlota 34 22 #NAME?
#NAME?
Goal: Convert Cross Tabulated Report into Proper Data Set with Formula #NAME?
#NAME?
Row C. 3 #NAME?
Column C. 2 #NAME?

Prod Prod SR SR Sales Err:509

#NAME? #NAME? #NAME? #NAME? #NAME? Err:509
#NAME? #NAME? #NAME? #NAME? #NAME? Err:509
#NAME? #NAME? #NAME? #NAME? #NAME? Err:509
#NAME? #NAME? #NAME? #NAME? #NAME? Err:509
#NAME? #NAME? #NAME? #NAME? #NAME? Err:509
#NAME? #NAME? #NAME? #NAME? #NAME?

Time1 Time2 Time3 Time4 Time5

0.101 0.177 0.103 0.108 0.163
0.106 0.103 0.112 0.174 0.105
0.1 0.104 0.104 0.104 0.0999
0.106 0.105 0.108 0.106 0.111
0.134 0.131 0.117 0.106 0.1
0.106 0.115 0.097 0.108 0.085
Ave #NAME? #NAME? #NAME? #NAME? #NAME?
<
<
<

<
<
<

INDEX formula is faster

Solution from victor at YouTube Comments:

SR Sales
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L M N O P
1 VSTACK
2 Quarter 1 Quarter 2 Re-Orient Data
Date Student Test Score Date Student Test Score Functions CHOOSECOLS
3 1/6/2023 Sioux Test 1 87 3/1/2023 Sioux Test 4 87 CHOOSECOLS SORT
4 2/10/2023 Sioux Test 2 86 3/15/2023 Sioux Test 5 86 CHOOSEROWS TAKE
5 2/22/2023 Sioux Test 3 70 4/3/2023 Sioux Test 6 70 DROP
6 1/6/2023 Chantel Test 1 96 3/1/2023 Chantel Test 4 87 TAKE Goal:
7 2/10/2023 Chantel Test 2 90 3/15/2023 Chantel Test 5 90 VSTACK Top Five Student Sorted Scores
8 2/22/2023 Chantel Test 3 90 4/3/2023 Chantel Test 6 90 HSTACK from both tables
9 1/6/2023 Mo Test 1 89 3/1/2023 Mo Test 4 89 SORT
10 2/10/2023 Mo Test 2 45 3/15/2023 Mo Test 5 90 TOCOL Top: 6
11 2/22/2023 Mo Test 3 93 4/3/2023 Mo Test 6 93 TOROW
12 1/6/2023 Ty Test 1 99 3/1/2023 Ty Test 4 99 TRANSPOSE Student ALL Scores
13 2/10/2023 Ty Test 2 92 3/15/2023 Ty Test 5 90 WRAPCOLS #NAME? #NAME?
14 2/22/2023 Ty Test 3 92 4/3/2023 Ty Test 6 92 WRAPROWS #NAME? #NAME?
15 SORTBY #NAME? #NAME?
16 UNIQUE #NAME? #NAME?
17 Top 4 include ties: EXPAND #NAME? #NAME?
18 Top 4 FILTER #NAME? #NAME?
19 Value 93
20 Err:509
21 Student ALL Scores
22 #NAME? #NAME? ###
23 #NAME? #NAME?
24 #NAME? #NAME?
25 #NAME? #NAME?
26 #NAME? #NAME?

890002248.xlsx - Re-Orient (an) Page 60 of 124

 MECS Video #9 - Array Formula Notes

Q R S T U V W X Y Z AA AB AC AD
1 TOROW
2
TRANSPOSE
3
4 Goal:
5 Orient test scores from second table horizontally
6
7 Tests: #NAME?
8
9 Test: Test 4 Test 5 Test 6 Test 4 Test 5 Test 6 Test 4 Test 5 Test 6 Test 4 Test 5 Test 6
10 Score: 87 86 70 87 90 90 89 90 93 99 90 92
11
12
13 Err:509
14 Err:509
15

890002248.xlsx - Re-Orient (an) Page 61 of 124

 MECS Video #9 - Array Formula Notes

AF AG AH AI AJ AK AL AM AN AO AP AQ
1 WRAPCOLS SORTBY FILTER Date
2
UNIQUE EXPAND 3/15/2023
3 Goal:
4 Create Rectangle of Test Scores in Cells Goal: Goal:
5 Extract unique list of test scores Show tests after March 15,
6 #NAME? #NAME? #NAME? #NAME? sorted by Test, then by Score with word Last in cell to left
7 #NAME? #NAME? #NAME? #NAME?
8 #NAME? #NAME? #NAME? #NAME? Test Score Score Label
9 #NAME? #NAME? #NAME? #NAME?
10 Err:509 #NAME? #NAME? #NAME? #NAME?
11 #NAME? #NAME? #NAME? #NAME?
12 #NAME? #NAME? #NAME? #NAME?
13 #NAME? #NAME?
14 #NAME? #NAME? Score Label
15 #NAME? #NAME? #NAME? #NAME?
16 #NAME? #NAME? #NAME? #NAME?
17 #NAME? #NAME? #NAME? #NAME?
18 #NAME? #NAME?
19
20
21
22
23
24 Err:509
25 Err:509
26 Err:509
27 Err:509
28
29 Test Score
30 #NAME? #NAME?
31 #NAME? #NAME?
32 #NAME? #NAME?
33 #NAME? #NAME?
34 #NAME? #NAME?
35 #NAME? #NAME?
36 #NAME? #NAME?
37 #NAME? #NAME?
38 #NAME? #NAME?
890002248.xlsx - Re-Orient (an) Page 62 of 124
 MECS Video #9 - Array Formula Notes

AS AT AU AV AW AX AY AZ BA BB
1 VSTACK
2
SORT In 2024
3 Product Sales UNIQUE Microsoft added new functions:
4 Quad 439.43 DROP GROUPBY
5 Carlota 231.05 and
6 Quad 120.01 Goal: PIVOTBY
7 Aspen 550.51 Create Dynamic Product Sales Report

>
>
>
8 Aspen 400.1
9 Carlota 397.84 Product Sales Product
10 #NAME? #NAME? #NAME?
11 #NAME? #NAME? #NAME?
12 #NAME? #NAME? #NAME?
13 New Data: #NAME? #NAME? #NAME?
14
15 Yanaki 129.27
16 Carlota 388.49 Err:509
17 Yanaki 43.69 Err:509
18
19 Err:509
20 Err:509
21

890002248.xlsx - Re-Orient (an) Page 63 of 124

 MECS Video #9 - Array Formula Notes

BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR
1

2
Prod/SR Joe Sioux Solution from victor at YouTube Comments:
ew functions:
3 Quad 12 24
4 Aspen 11 14 Pro SR Sales
5 Carlota 34 22 #NAME? #NAME? #NAME?
6 #NAME? #NAME? #NAME?
7 Goal: Convert Cross Tabulated Report into Proper Data Set with Formula #NAME? #NAME? #NAME?
8 #NAME? #NAME? #NAME?
9 Row C. 3 #NAME? #NAME? #NAME?
10 Column C. 2 #NAME? #NAME? #NAME?
11
12 Prod Prod SR SR Sales Err:509
13 #NAME? #NAME? #NAME? #NAME? #NAME? Err:509
14 #NAME? #NAME? #NAME? #NAME? #NAME? Err:509
15 #NAME? #NAME? #NAME? #NAME? #NAME? Err:509
16 #NAME? #NAME? #NAME? #NAME? #NAME? Err:509
17 #NAME? #NAME? #NAME? #NAME? #NAME? Err:509
18 #NAME? #NAME? #NAME? #NAME? #NAME?
19
20
21 Time1 Time2 Time3 Time4 Time5
22 0.101 0.177 0.103 0.108 0.163
23 0.106 0.103 0.112 0.174 0.105
24 0.1 0.104 0.104 0.104 0.0999
25 0.106 0.105 0.108 0.106 0.111
26 0.134 0.131 0.117 0.106 0.1
27 0.106 0.115 0.097 0.108 0.085
28 Ave #NAME? #NAME? #NAME? #NAME? #NAME?

890002248.xlsx - Re-Orient (an) Page 64 of 124

 Rows 10 Products
Columns 2 Quad
Create Arrays of
Numbers Min 1 Yanaki
RANDARRAY Max 5 Carlota
SEQUENCE Bellen
RANDARRAY

0 to 1 1 to 5 Rounded Text Col1 Col2

Rows 10 Insurance Agent has 6 appointments.
Columns 2 It is known from past data that she ma

Start 5 What is probability that she will make

Step 5

SEQUENCE Binomial Distribution

P 0.2
Goal: Spill Repeated Word n 6

Seq1 Seq5 Date Sales SalesRep X P(x)

11/19/2022 775.17
11/20/2022 631.59
11/21/2022 1929.39
11/22/2022 688.83
11/23/2022 167.37
11/24/2022 765.01
11/25/2022 936.83
11/26/2022 930.98

Goal: Investigate characters in text string:

The dog�is rad

Characters: Code:
 What it
looks like
with
=CHAR(#)
Number What it is function
#NAME? null #NAME?
#NAME? start of hea #NAME?
#NAME? start of text #NAME?
#NAME? end of text #NAME?
#NAME? end of tran #NAME?
#NAME? inquiry #NAME?
#NAME? acknowledg #NAME?
#NAME? bell #NAME?
#NAME? backspace #NAME?
#NAME? horizontal t #NAME?
#NAME? line feed/ne #NAME?
#NAME? vertical tab #NAME?
#NAME? form feed/ #NAME?
#NAME? carriage ret #NAME?
#NAME? shift out #NAME?
#NAME? shift in #NAME?
#NAME? data link e #NAME?
#NAME? device cont #NAME?
#NAME? device cont #NAME?
#NAME? device cont #NAME?
#NAME? device cont #NAME?
#NAME? negative a #NAME?
#NAME? synchronous#NAME?
#NAME? end of tran #NAME?
#NAME? cancel #NAME?
#NAME? end of med #NAME?
#NAME? substitute #NAME?
#NAME? escape #NAME?
#NAME? file separat #NAME?
#NAME? group separ #NAME?
#NAME? record sepa #NAME?
#NAME? unit separa #NAME?
#NAME? space #NAME?
#NAME? ! #NAME?
#NAME? " #NAME?
#NAME? # #NAME?
#NAME? $ #NAME?
#NAME? % #NAME?
#NAME? & #NAME?
#NAME? ' #NAME?
#NAME? ( #NAME?
#NAME? ) #NAME?
#NAME? * #NAME?
#NAME? + #NAME?
#NAME? , #NAME?
#NAME? - #NAME?
#NAME? . #NAME?
#NAME? / #NAME?
#NAME? 0 #NAME?
#NAME? 1 #NAME?
#NAME? 2 #NAME?
#NAME? 3 #NAME?
#NAME? 4 #NAME?
#NAME? 5 #NAME?
#NAME? 6 #NAME?
#NAME? 7 #NAME?
#NAME? 8 #NAME?
#NAME? 9 #NAME?
#NAME? : #NAME?
#NAME? ; #NAME?
#NAME? < #NAME?
#NAME? = #NAME?
#NAME? > #NAME?
#NAME? ? #NAME?
#NAME? @ #NAME?
#NAME? A #NAME?
#NAME? B #NAME?
#NAME? C #NAME?
#NAME? D #NAME?
#NAME? E #NAME?
#NAME? F #NAME?
#NAME? G #NAME?
#NAME? H #NAME?
#NAME? I #NAME?
#NAME? J #NAME?
#NAME? K #NAME?
#NAME? L #NAME?
#NAME? M #NAME?
#NAME? N #NAME?
#NAME? O #NAME?
#NAME? P #NAME?
#NAME? Q #NAME?
#NAME? R #NAME?
#NAME? S #NAME?
#NAME? T #NAME?
#NAME? U #NAME?
#NAME? V #NAME?
#NAME? w #NAME?
#NAME? X #NAME?
#NAME? Y #NAME?
#NAME? Z #NAME?
#NAME? [ #NAME?
#NAME? \ #NAME?
#NAME? ] #NAME?
#NAME? ^ #NAME?
#NAME? _ #NAME?
#NAME? ` #NAME?
#NAME? a #NAME?
#NAME? b #NAME?
#NAME? c #NAME?
#NAME? d #NAME?
#NAME? e #NAME?
#NAME? f #NAME?
#NAME? g #NAME?
#NAME? h #NAME?
#NAME? i #NAME?
#NAME? j #NAME?
#NAME? k #NAME?
#NAME? l #NAME?
#NAME? m #NAME?
#NAME? n #NAME?
#NAME? o #NAME?
#NAME? p #NAME?
#NAME? q #NAME?
#NAME? r #NAME?
#NAME? s #NAME?
#NAME? t #NAME?
#NAME? u #NAME?
#NAME? v #NAME?
#NAME? w #NAME?
#NAME? x #NAME?
#NAME? y #NAME?
#NAME? z #NAME?
#NAME? { #NAME?
#NAME? | #NAME?
#NAME? } #NAME?
#NAME? ~ #NAME?
#NAME? DEL #NAME?
#NAME? Ç #NAME?
#NAME? ü #NAME?
#NAME? é #NAME?
#NAME? â #NAME?
#NAME? ä #NAME?
#NAME? à #NAME?
#NAME? å #NAME?
#NAME? ç #NAME?
#NAME? ê #NAME?
#NAME? ë #NAME?
#NAME? è #NAME?
#NAME? ï #NAME?
#NAME? î #NAME?
#NAME? ì #NAME?
#NAME? Ä #NAME?
#NAME? Å #NAME?
#NAME? É #NAME?
#NAME? æ #NAME?
#NAME? Æ #NAME?
#NAME? ô #NAME?
#NAME? ö #NAME?
#NAME? ò #NAME?
#NAME? û #NAME?
#NAME? ù #NAME?
#NAME? ÿ #NAME?
#NAME? Ö #NAME?
#NAME? Ü #NAME?
#NAME? ¢ #NAME?
#NAME? £ #NAME?
#NAME? ¥ #NAME?
#NAME? ₧ #NAME?
#NAME? ƒ #NAME?
#NAME? á #NAME?
#NAME? í #NAME?
#NAME? ó #NAME?
#NAME? ú #NAME?
#NAME? ñ #NAME?
#NAME? Ñ #NAME?
#NAME? ª #NAME?
#NAME? º #NAME?
#NAME? ¿ #NAME?
#NAME? ⌐ #NAME?
#NAME? ¬ #NAME?
#NAME? ½ #NAME?
#NAME? ¼ #NAME?
#NAME? ¡ #NAME?
#NAME? « #NAME?
#NAME? » #NAME?
#NAME? ░ #NAME?
#NAME? ▒ #NAME?
#NAME? ▓ #NAME?
#NAME? │ #NAME?
#NAME? ┤ #NAME?
#NAME? ╡ #NAME?
#NAME? ╢ #NAME?
#NAME? ╖ #NAME?
#NAME? ╕ #NAME?
#NAME? ╣ #NAME?
#NAME? ║ #NAME?
#NAME? ╗ #NAME?
#NAME? ╝ #NAME?
#NAME? ╜ #NAME?
#NAME? ╛ #NAME?
#NAME? ┐ #NAME?
#NAME? └ #NAME?
#NAME? ┴ #NAME?
#NAME? ┬ #NAME?
#NAME? ├ #NAME?
#NAME? ─ #NAME?
#NAME? ┼ #NAME?
#NAME? ╞ #NAME?
#NAME? ╟ #NAME?
#NAME? ╚ #NAME?
#NAME? ╔ #NAME?
#NAME? ╩ #NAME?
#NAME? ╦ #NAME?
#NAME? ╠ #NAME?
#NAME? ═ #NAME?
#NAME? ╬ #NAME?
#NAME? ╧ #NAME?
#NAME? ╨ #NAME?
#NAME? ╤ #NAME?
#NAME? ╥ #NAME?
#NAME? ╙ #NAME?
#NAME? Ô #NAME?
#NAME? ╒ #NAME?
#NAME? ╓ #NAME?
#NAME? ╫ #NAME?
#NAME? ╪ #NAME?
#NAME? ┘ #NAME?
#NAME? ┌ #NAME?
#NAME? █ #NAME?
#NAME? ▄ #NAME?
#NAME? ▌ #NAME?
#NAME? ▐ #NAME?
#NAME? ▀ #NAME?
#NAME? α #NAME?
#NAME? ß #NAME?
#NAME? Γ #NAME?
#NAME? π #NAME?
#NAME? Σ #NAME?
#NAME? σ #NAME?
#NAME? µ #NAME?
#NAME? τ #NAME?
#NAME? Φ #NAME?
#NAME? Θ #NAME?
#NAME? Ω #NAME?
#NAME? δ #NAME?
#NAME? ∞ #NAME?
#NAME? φ #NAME?
#NAME? ε #NAME?
#NAME? ∩ #NAME?
#NAME? ≡ #NAME?
#NAME? ± #NAME?
#NAME? ≥ #NAME?
#NAME? ≤ #NAME?
#NAME? ⌠ #NAME?
#NAME? ⌡ #NAME?
#NAME? ÷ #NAME?
#NAME? ≈ #NAME?
#NAME? ≈ #NAME?
#NAME? ∙ #NAME?
#NAME? · #NAME?
#NAME? √ #NAME?
#NAME? ⁿ #NAME?
#NAME? ² #NAME?
#NAME? ■ #NAME?
#NAME? #NAME?
#NAME? #NAME?
 Agent has 6 appointments.
n from past data that she makes a sale 20.0% of the time.

robability that she will make 3 sales?

Chart Title: Average Test Score = 75, Stand. Dev = 10
Average Test Sco 75
Distribution Stand. Dev 10
Start 34
End 114

X = Test Score P(x) Err:509

Average Test Score = 75, Stand. Dev = 10
1
0.9
0.8
0.7
0.6
P(x) 0.5
0.4
0.3
0.2
0.1
0
X = Test Score
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L M N O P Q R S T U V W
1 Rows 10 Products Rows 10 Insurance Agent has 6 appointments.
2 Columns 2 Quad Columns 2 It is known from past data that she makes a sale 20.0% of the time.
3 Create Arrays of
Numbers Min 1 Yanaki Start 5 What is probability that she will make 3 sales?
4 RANDARRAY Max 5 Carlota Step 5
5 SEQUENCE Bellen Average Test Sco 75
6 RANDARRAY SEQUENCE Binomial Distribution Stand. Dev 10
7 P 0.2 Start 34
8 Goal: Spill Repeated Word n 6 End 114
9
10 0 to 1 1 to 5 Rounded Text Col1 Col2 Seq1 Seq5 Date Sales SalesRep X P(x) X = Test Score P(x)
11 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 11/19/2022 775.17 #NAME? #NAME? #NAME? #NAME? #NAME?
12 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 11/20/2022 631.59 #NAME? #NAME? #NAME? #NAME? #NAME?
13 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 11/21/2022 1929.39 #NAME? #NAME? #NAME? #NAME? #NAME?
14 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 11/22/2022 688.83 #NAME? #NAME? #NAME? #NAME? #NAME?
15 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 11/23/2022 167.37 #NAME? #NAME? #NAME? #NAME? #NAME?
16 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 11/24/2022 765.01 #NAME? #NAME? #NAME? #NAME? #NAME?
17 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 11/25/2022 936.83 #NAME? #NAME? #NAME? #NAME? #NAME?
18 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 11/26/2022 930.98 #NAME? #NAME? #NAME?
19 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
20 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? Goal: Investigate characters in text string: #NAME? #NAME?
21 #NAME? #NAME?
22 Err:509 The dog�is rad Average Test Score = 75, Stand. Dev = 10
#NAME? #NAME?
23 Err:509 1 #NAME? #NAME?
24 Err:509 Characters: Code: 0.8 #NAME? #NAME?
25 Err:509 #NAME? #NAME? 0.6 #NAME? #NAME?
0.4
26 Err:509 #NAME? #NAME? #NAME? #NAME?

P(x)
0.2
27 Err:509 #NAME? #NAME? 0 #NAME? #NAME?
28 Err:509 #NAME? #NAME? E? E? E? E? E? E? E? E? E? E? #NAME?
E? E? E? E? E? #NAME?
E?
29 Err:509 #NAME? #NAME? AM AM AM AM AM AM AM AM AM AM AM AM AM AM AM AM#NAME?
#NAME?
# N #N # N # N # N #N # N #N # N #N #N # N # N # N #N # N
30 Err:509 #NAME? #NAME? #NAME? #NAME?
X = Test Score
31 Err:509 #NAME? #NAME? #NAME? #NAME?
32 Err:509 #NAME? #NAME? #NAME? #NAME?
33 Err:509 #NAME? #NAME? #NAME? #NAME?
34 Err:509 #NAME? #NAME? #NAME? #NAME?
35 Err:509 #NAME? #NAME? #NAME? #NAME?
36 #NAME? #NAME? #NAME? #NAME?
37 #NAME? #NAME? #NAME? #NAME?
38 #NAME? #NAME? #NAME? #NAME?

890002248.xlsx - Create No. Array (an) Page 77 of 124

 FREQUENCY

Statistics & Upper Limit

Matrix Algebra Cola Sales ($) for Sales ($) Frequency Categories for Upper Limits
FREQUENCY Coke 250 250 Sales <= 250
GROWTH Pepsi 504 450 250 < Sales <=450
LINEST Bloxy 331 750 450 < Sales <=750
LOGEST Bloxy 1047 1050 750 < Sales <=1050
MMULT c 275 Sales >1050
MODE.MULT RC 1005
MUNIT RC 616
TREND Bloxy 1043 MODE.MULT
RC 1054
RC 275 Mode = Number That Occurs Most Frequently
RC 271
c 604 Modes:
RC 923
Bloxy 613
Pepsi 474
Pepsi 923 Err:509
RC 678
Pepsi 169

Raw Data:  Counting based on an upper limit:

Test Scores Frequency Distribution #1:
43 Test Grades From
57 Test Score Upper Limits Frequency
68 10 0
50 20 0
69 30 1
Frequency

75 40 1
66 50 3
90 60 4
41 70 5
37 80 4
80 90 2
10 20 30 40 50 60
 50 20 0
69 30 1

Frequency
75 40 1
66 50 3
90 60 4
41 70 5
37 80 4
80 90 2
10 20 30 40 50 60
53 100 1
61 >100 0 Test Score Up
78
90 < Hidden data rows
56

Raw Data:  Counting based on an upper limit:

$ Sales at Target Frequency Distribution #2:
$15.21 Pattern for $ Sale
$8.80 $ Sales Upper Limits Frequency 19
$22.26 $25 19
$35.30 $75 10
$15.53 $125 4

Frequency
10
$19.12 $175 2
$25.53 $225 1
$27.58 $275 0 4
$182.88 $325 1 2
1
$5.47 $375 0 0
$14.74 $425 0
$25 $75 $125 $175 $225 $275
$17.56 $475 0
$9.94 >$475 2 $ SalesUppe
$44.21 < Hidden data rows
$4.62
 TREND Linear Regression to estimate based on X & Y.

Sales Calls Call Estimates Sales Sales

Made (X) Sales (Y) Day (X) Estimates (Y) Estimates (Y) Slope
156 96,408 Day 1 52 822.2851123
39 22,737 Day 2 67 Y-intercept
121 95,832 Day 3 133 -8101.44575
68 35,496 Day 4 160
30 20,190 Day 5 111
48 37,968 Day 6 26 Field: Sales Calls Made
157 140,358 Day 7 136
103 74,572 Day 8 126 160,000
74 43,734 Day 9 159 140,000
87 60,030 Day 10 70
120,000
165 112,200 Day 11 163
100,000
63 58,212

Sales (Y)
148 141,192 80,000

64 36,416 LINEST 60,000

51 40,851 Calculates Linear Regression Statistics. 40,000
68 39,916 20,000
Slope Y-intercept
0
20 40 60

LINEST

Slope m 822.2851123 -8101.445746 Intercept b

Standard Error m 78.060683555 7831.5915034 Standard Error b
Coefficient of Determination 0.8879672883 13763.440044 Standard Error y
F 110.96350203 14 df
SS Regression 21020069392 2652051946 SS Residual
const: = 1 = TRUE = b calculated normally. 0 = FALSE = b set to zero.
stats: 1 = TRUE = calculate all statistics. 0 = FALSE = calculate just m and b.

Test Grades From Excel Class

30 40 50 60 70 80 90 100 >100
30 40 50 60 70 80 90 100 >100
Test Score Upper Limts

Pattern for $ Sales at Target

4
2 2
1 1
0 0 0 0

$125 $175 $225 $275 $325 $375 $425 $475 >$475

$ SalesUpper Limts
 Field: Sales Calls Made (X) and Field: Sales (Y) appear highly
correlated.
60,000
40,000
20,000
00,000
80,000
60,000
40,000
20,000
0
20 40 60 80 100 120 140 160 180
Sales Calls Made (X)
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J K L M N O P Q R S T
1 FREQUENCY TREND Linear Regression to estimate based on X & Y.
2

3 Statistics & Upper Limit Sales Calls Call Estimates Sales Sales
Matrix Algebra Cola Sales ($) for Sales ($) Frequency Categories for Upper Limits Made (X) Sales (Y) Day (X) Estimates (Y) Estimates (Y) Slope
4 FREQUENCY Coke 250 250 2 Sales <= 250 156 96,408 Day 1 52 34,657 34,657 822.285112
5 GROWTH Pepsi 504 450 4 250 < Sales <=450 39 22,737 Day 2 67 46,992 46,992 Y-intercept
6 LINEST Bloxy 331 750 6 450 < Sales <=750 121 95,832 Day 3 133 101,262 101,262 -8101.44575
7 LOGEST Bloxy 1047 1050 5 750 < Sales <=1050 68 35,496 Day 4 160 123,464 123,464
8 MMULT c 275 1 Sales >1050 30 20,190 Day 5 111 83,172 83,172
9 MODE.MULT RC 1005 48 37,968 Day 6 26 13,278 13,278
10 MUNIT RC 616 157 140,358 Day 7 136 103,729 103,729
11 TREND Bloxy 1043 MODE.MULT 103 74,572 Day 8 126 95,506 95,506
12 RC 1054 74 43,734 Day 9 159 122,642 122,642
13 RC 275 Mode = Number That Occurs Most Frequently 87 60,030 Day 10 70 49,459 49,459
14 RC 271 165 112,200 Day 11 163 125,931 125,931
15 c 604 Modes: 63 58,212
16 RC 923 275 148 141,192 Err:509
17 Bloxy 613 923 64 36,416 Err:509
18 Pepsi 474 51 40,851
19 Pepsi 923 Err:509 68 39,916 LINEST
20 RC 678 Err:509 Calculates Linear Regression Statistics.
21 Pepsi 169
22 Slope Y-intercept
23 822.2851123 -8101.445746
24
25 Err:509
26 Err:509
27
28 LINEST
29
30 Slope m 822.2851123 -8101.445746 Intercept b
31 Standard Error m 78.060683555 7831.5915034 Standard Error b
32 Coefficient of Determination 0.8879672883 13763.440044 Standard Error y
33 F 110.96350203 14 df
34 SS Regression 21020069392 2652051946 SS Residual
35 const: = 1 = TRUE = b calculated normally. 0 = FALSE = b set to zero.
36 stats: 1 = TRUE = calculate all statistics. 0 = FALSE = calculate just m and b.
37

890002248.xlsx - Stats (an) Page 83 of 124

 TEXTSPLIT - row
FILTERXML
TEXT Text delimiter
TEXTSPLIT 2,23,43,6,90,690 2
FILTERXML 23
43
6
90
690

TEXTSPLIT - row &

Text column delimiter
2,23;43,6;90,690

TEXTSPLIT - 2
delimters for row

Err:509
 MECS Video #9 - Array Formula Notes

A B C D E F G H I J
1
TEXTSPLIT - row
2 FILTERXML
TEXTSPLIT Text delimiter
3 TEXTSPLIT 2,23,43,6,90,690 #NAME? 2
4 FILTERXML #NAME? 23
5 #NAME? 43
6 #NAME? 6
7 #NAME? 90
8 #NAME? 690
9
TEXTSPLIT - row &
10 column delimiter
Text
11 2,23;43,6;90,690 #NAME? #NAME?
12 #NAME? #NAME?
13 #NAME? #NAME?
14
TEXTSPLIT - 2
15 delimters for row
Text
16 2,23;43,6;90,690 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
17
18 Err:509
19 Err:509
20 Err:509
21 Err:509
22

890002248.xlsx - Text (an) Page 86 of 124

Goal: In cell D7 create a formula that calculates the total bank deposit based on the bills and number of bills.

Bills ($ currency) $1 $5 $10 $20 $50 $100

Number Bills 75 25 22 142 5 43

Total Deposit
number of bills.
Goal: In cell D7 create a formula that calculates the total bank deposit based on the bills and number of bills.

Bills ($ currency) $1 $5 $10 $20 $50 $100

Number Bills 75 25 22 142 5 43

Total Deposit $7,810

Formula in cell D7:

{=SUMPRODUCT(D4:I4*D5:I5)}
number of bills.
Goal: In cell E14 create a formula that calculates the expected portfolio returns.
Formula should sum:
'probability of economic state' * 'weight of stock in portfolio' * 'estimates returns'.

Weight of Stock in Portfolio: 0.3 0.4 0.3

Stock A Full Stock B Full Stock C Full
Probability of Estimated Estimated Estimated
Economic State Return Return Return
Bad 0.15 0.00 -0.15 -0.20
OK 0.3 0.03 -0.02 0.01
Good 0.35 0.05 0.11 0.08
Great 0.2 0.10 0.20 0.20

Expected Portfolio Returns:

Goal: In cell E14 create a formula that calculates the expected portfolio returns.
Formula should sum:
'probability of economic state' * 'weight of stock in portfolio' * 'estimates returns'.

Weight of Stock in Portfolio: 0.3 0.4 0.3

Stock A Full Stock B Full Stock C Full
Probability of Estimated Estimated Estimated
Economic State Return Return Return
Bad 0.15 0.00 -0.15 -0.20
OK 0.3 0.03 -0.02 0.01
Good 0.35 0.05 0.11 0.08
Great 0.2 0.10 0.20 0.20

Expected Portfolio Returns: 0.04625

Formula in cell E14: {=SUM(C9:C12*D7:F7*D9:F12)}

Goal: Somewhere in the worksheet create a formula report that shows the total call times for each sales rep.
Add conditional formatting that formats the report depending on how many rows are spilled in the report.

Time on Calls
Sales Rep Date (min)
Chun 1/1/2022 218
Xiaver 1/1/2022 418
Shinia 1/1/2022 332
Billy 1/1/2022 248
Chun 1/2/2022 343
Xiaver 1/2/2022 183
Shinia 1/2/2022 223
Billy 1/2/2022 379
Chun 1/3/2022 389
Xiaver 1/3/2022 444
Shinia 1/3/2022 473
Billy 1/3/2022 195
Chun 1/4/2022 192
Xiaver 1/4/2022 217
Shinia 1/4/2022 122
each sales rep.
lled in the report.
Goal: Somewhere in the worksheet create a formula report that shows the total call times for each sales rep.
Add conditional formatting that formats the report depending on how many rows are spilled in the report.

Time on Calls Total Time

Sales Rep Date (min) Sales Rep on Calls
Chun 1/1/2022 218 #NAME? #NAME? Formula in cell G6:
Xiaver 1/1/2022 418 #NAME? #NAME? {=_xlfn._xlws.sort(_xlfn.unique($
Shinia 1/1/2022 332 #NAME? #NAME?
Billy 1/1/2022 248 #NAME? #NAME? Formula in cell H6:
Chun 1/2/2022 343 {=SUMIFS($'HW(3an)'.$E$6:$E$2
Xiaver 1/2/2022 183
Shinia 1/2/2022 223
Billy 1/2/2022 379
Chun 1/3/2022 389
Xiaver 1/3/2022 444
Shinia 1/3/2022 473
Billy 1/3/2022 195
Chun 1/4/2022 192
Xiaver 1/4/2022 217
Shinia 1/4/2022 122
each sales rep.
lled in the report.

xlws.sort(_xlfn.unique($'HW(3an)'.$C$6:$C$20))}

S($'HW(3an)'.$E$6:$E$20,$'HW(3an)'.$C$6:$C$20,_xlfn.anchorarray(G6))}
Goal 1: As shown at the 23:08 minute mark in MECS video #9, Create a profit budget in the tempalte below:

Budget Income Statement

by Month Jan Feb Mar Apr
Expenses as % of Sales % Revenue 14,207 13,707 13,582 17,747
COGS Expense 0.275 COGS Expense
Research Expense 0.0875 Research Expense
Selling & Marketing Ex. 0.0975 Selling & Marketing Ex.
Operations Expense 0.14 Operations Expense
Administration Expense 0.07 Administration Expense
Interest Expense 0.025 Interest Expense
Income Tax Expense 0.055 Income Tax Expense
Other Expense 0.024 Other Expense
0.774 Total Expenses 0 0 0 0
Net Income (Profit)

Err:509
May Jun Jul Aug Sep Oct Nov Dec Total
18,907 10,659 10,707 10,332 18,257 10,582 11,157 11,482 161,326
0
0
0
0
0
0
0
0
0 0 0 0 0 0 0 0 0
Goal 1: As shown at the 23:08 minute mark in MECS video #9, Create a profit budget in the tempalte below:

Budget Income Statement

by Month Jan Feb Mar
Expenses as % of Sales % Revenue 14,207 13,707 13,582
COGS Expense 0.275 COGS Expense 3,907 3,769 3,735
Research Expense 0.0875 Research Expense 1,243 1,199 1,188
Selling & Marketing Ex. 0.0975 Selling & Marketing Ex. 1,385 1,336 1,324
Operations Expense 0.14 Operations Expense 1,989 1,919 1,901
Administration Expense 0.07 Administration Expense 994 959 951
Interest Expense 0.025 Interest Expense 355 343 340
Income Tax Expense 0.055 Income Tax Expense 781 754 747
Other Expense 0.024 Other Expense 341 329 326
0.774 Total Expenses 10,996 10,609 10,512
Net Income (Profit) 3,211 3,098 3,070

Err:509
mpalte below:

Apr May Jun Jul Aug Sep Oct Nov Dec Total
17,747 18,907 10,659 10,707 10,332 18,257 10,582 11,157 11,482 161,326
4,880 5,199 2,931 2,944 2,841 5,021 2,910 3,068 3,158 44,365
1,553 1,654 933 937 904 1,597 926 976 1,005 14,116
1,730 1,843 1,039 1,044 1,007 1,780 1,032 1,088 1,120 15,729
2,485 2,647 1,492 1,499 1,446 2,556 1,481 1,562 1,607 22,586
1,242 1,323 746 749 723 1,278 741 781 804 11,293
444 473 266 268 258 456 265 279 287 4,033
976 1,040 586 589 568 1,004 582 614 632 8,873
426 454 256 257 248 438 254 268 276 3,872
13,736 14,634 8,250 8,287 7,997 14,131 8,190 8,636 8,887 124,866
4,011 4,273 2,409 2,420 2,335 4,126 2,392 2,521 2,595 36,460
Goal:
Using formulas, create a Dynamic Product Sales Report with Total Rows

Date Product Customer Sales ($) Product Sales ($)

### Carlota Customer 36,368.19
### Aspen Customer 32,532.89
### Yanaki Customer 27,638.77
### Aspen Customer 14,635.89
7/9/2022 Yanaki Customer 34,372.40
7/7/2022 Yanaki Customer 37,088.49
7/3/2022 Yanaki Customer 24,704.79
### Aspen Customer 38,171.46
### Quad Customer 28,347.85
7/6/2022 Carlota Customer 27,474.82

Add New Records to bottom to test dynamic report:

7/4/2022 Yanaki Customer 34,704.79

### Bellen Customer 48,171.46
### Bellen Customer 58,347.85
### Bellen Customer 62,746.14
Goal:
Using formulas, create a Dynamic Product Sales Report with Total Rows

Date Product Customer Sales ($) Product Sales ($)

7/19/2022 Carlota Customer 20 36,368.19 #NAME? #NAME?
7/17/2022 Aspen Customer 6 32,532.89 #NAME? #NAME?
7/10/2022 Yanaki Customer 20 27,638.77 #NAME? #NAME?
7/17/2022 Aspen Customer 15 14,635.89 #NAME? #NAME?
7/9/2022 Yanaki Customer 14 34,372.40 #NAME? #NAME?
7/7/2022 Yanaki Customer 16 37,088.49 #NAME? #NAME?
7/3/2022 Yanaki Customer 10 24,704.79
7/13/2022 Aspen Customer 27 38,171.46
7/18/2022 Quad Customer 19 28,347.85
7/6/2022 Carlota Customer 5 27,474.82
7/4/2022 Yanaki Customer 1 34,704.79
7/11/2022 Bellen Customer 2 48,171.46
7/28/2022 Bellen Customer 14 58,347.85
7/20/2022 Bellen Customer 22 62,746.14

Add New Records to bottom to test dynamic report:

7/4/2022 Yanaki Customer 1 34,704.79

7/11/2022 Bellen Customer 2 48,171.46
7/28/2022 Bellen Customer 14 58,347.85
7/20/2022 Bellen Customer 22 62,746.14
Goal 2: Multiply each test score by each test weight and then add to get student total score.
#NAME?

Name Test 1 Test 2 Test 3 Test 4 Weighted Ave.

Sioux 91 94 91 87.5
Chin 98 87.5 79 86
Ty 73 36 56 78
Mo 65 70 72 84

Test Weights
Test 1 15%
Test 2 20%
Test 3 15%
Test 4 50%
Goal 2: Multiply each test score by each test weight and then add to get student total score.
#NAME?

Name Test 1 Test 2 Test 3 Test 4 Weighted Ave.

Sioux 91 94 91 87.5 89.85 Err:509
Chin 98 87.5 79 86 #NAME?
Ty 73 36 56 78 #NAME?
Mo 65 70 72 84 #NAME?

Test Weights
Test 1 15%
Test 2 20%
Test 3 15%
Test 4 50%
From the proper data set below, using formulas, create the report shown to the right = >
Hint 1: to get the Dates and Products in the top rows, you can use UNIQUE function inside TOROW function.
Hint 2: to get the rectangle of numbers you can use the WRAPROWS function.

Date Product Sales

### Quad 44,617
### Carlota 34,971
### Aspen 32,846
### Yanaki 49,476
### Quad 10,076
### Carlota 40,788
### Aspen 43,246
### Yanaki 14,558
### Quad 16,395
### Carlota 40,859
### Aspen 36,957
### Yanaki 47,804
### Quad 40,274
### Carlota 48,957
### Aspen 25,277
### Yanaki 41,965
 Date/Product Sales:
OW function.
11/21/22 11/22/22 11/23/22 11/24/22
Quad Carlota Aspen Yanaki
44,617 34,971 32,846 49,476
10,076 40,788 43,246 14,558
16,395 40,859 36,957 47,804
40,274 48,957 25,277 41,965
From the proper data set below, using formulas, create the report shown to the right = >
Hint 1: to get the Dates and Products in the top rows, you can use UNIQUE function inside TOROW function.
Hint 2: to get the rectangle of numbers you can use the WRAPROWS function.

Date Product Sales Date/Product Sales:

### Quad 44,617
### Carlota 34,971 #NAME? #NAME? #NAME? #NAME? Err:509
### Aspen 32,846 #NAME? #NAME? #NAME? #NAME? Err:509
### Yanaki 49,476 #NAME? #NAME? #NAME? #NAME? Err:509
### Quad 10,076 #NAME? #NAME? #NAME? #NAME?
### Carlota 40,788 #NAME? #NAME? #NAME? #NAME?
### Aspen 43,246 #NAME? #NAME? #NAME? #NAME?
### Yanaki 14,558
### Quad 16,395
### Carlota 40,859
### Aspen 36,957
### Yanaki 47,804
### Quad 40,274
### Carlota 48,957
### Aspen 25,277
### Yanaki 41,965
OW function.
Goal: Extract records with top 5 scores, including records if there is a tie for 5th
Sort result by the numbers, biggest to smallest.

Name Score Top Top 5 hurdle Name Score

Shinea 25 5
Phil 10
Pham 10
Gigi 19
Ty 6
Chantel 19
Chin 15
Hue 23
Miki 10
Abdi 15
Tyrone 19
Sammi 22
 1 st
2 nd
3 rd
4 th
5 th
6 th
7 th
8 th
9 th
10 th
11 th
12 th
Goal: Extract records with top 5 scores, including records if there is a tie for 5th
Sort result by the numbers, biggest to smallest.

Name Score Top Top 5 hurdle Name Score

Shinea 25 5 19 #NAME? #NAME?
Phil 10 #NAME? #NAME?
Pham 10 #NAME? #NAME?
Gigi 19 #NAME? #NAME?
Ty 6 #NAME? #NAME?
Chantel 19 #NAME? #NAME?
Chin 15
Hue 23 Formula in cell G6: =LARGE($'HW(8an)'.$B$6:$C$17,E6)
Miki 10 Formula in cell I6: {=_xlfn._xlws.sort(_xlfn._xlws.filter($'HW(8an)'.$B$6:$C$17,$'HW(8an)'.$C$6
Abdi 15
Tyrone 19
Sammi 22
$B$6:$C$17,$'HW(8an)'.$C$6:$C$17>=G6),2,-1)}
 1 st
2 nd
3 rd
4 th
5 th
6 th
7 th
8 th
9 th
10 th
11 th
12 th
Goal: With the two data sets below, create two different qualitative frequency distributions.
Use the worksheet to make calculations and create your final reports.

Data Set #1 Data Set #2

Test Grades $ Sales at Target

43 $15.21
57 $8.80
68 $22.26 More data below
50 $35.30

>
>
>
69 $15.53
75 $19.12
66 $25.53
90 $27.58
41 $182.88
37 $5.47
80 $14.74
53 $17.56
61 $9.94
78 $26.29
90 $47.85
95 $275.75
27 $598.32
61 $5.06
78 $6.58
57 $11.60
56 $77.50
$98.00
$678.22
$170.00
$87.00
$69.43
$41.02
$7.33
$9.58
$18.75
$37.44
$33.47
$8.45
$2.36
$15.47
$168.95
$89.27
$44.21
$4.62
Goal: a.
Test Grades From Excel Class

Test Grades Category Option 1 Test Score Upper Limts Frequency

43 #NAME? #NAME? #NAME?
57 #NAME? #NAME? #NAME?
68 #NAME? #NAME? #NAME?
50 #NAME? #NAME? #NAME?
69 #NAME? #NAME? #NAME?
75 #NAME? #NAME? #NAME?
66 #NAME? #NAME? #NAME?
90 #NAME? #NAME? #NAME?
41 #NAME? #NAME? #NAME?
37 #NAME? #NAME? #NAME?
80 #NAME? #NAME? #NAME?
53
61
78
90
95 Test Grades From Excel Class
27
61
78
57
Frequency

56

#NAME #NAME #NAME #NAME #NAME #NAME #NAME #NAME #N

? ? ? ? ? ? ? ?
Test Score Upper Limts
 Formula:
#NAME?

SEQUENCE
rows 10
[columns]
[start] 10
[step] 10

ades From Excel Class

#NAME #NAME #NAME #NAME #NAME #NAME #NAME

? ? ? ? ? ? ?
Test Score Upper Limts
Goal: a.
Pattern for $ Sales at Target

$ Sales at Target Category Option 1 $ SalesUpper Limts Frequency

$15.21 #NAME? #NAME? #NAME?
$8.80 #NAME? #NAME? #NAME?
$22.26 #NAME? #NAME? #NAME?
$35.30 #NAME? #NAME? #NAME?
$15.53 #NAME? #NAME? #NAME?
$19.12 #NAME? #NAME? #NAME?
$25.53 #NAME? #NAME? #NAME?
$27.58 #NAME? #NAME? #NAME?
$182.88 #NAME? #NAME? #NAME?
$5.47 #NAME? #NAME? #NAME?
$14.74 #NAME? #NAME? #NAME?
$17.56
$9.94
$26.29
Pattern for $ Sales at Target
$47.85
$275.75
$598.32
$5.06
$6.58
Frequency

$11.60
$77.50
$98.00
$678.22
$170.00
#NAME #NAME #NAME #NAME #NAME #NAME #NAME #NAME #NAM
$87.00 ? ? ? ? ? ? ? ? ?
$69.43 $ SalesUpper Limts
$41.02
$7.33
$9.58
$18.75
$37.44
$33.47
$8.45
$2.36
$15.47
$168.95
$89.27
$44.21
$4.62
 Formula:
#NAME?

SEQUENCE
rows 10
[columns]
[start] 25
[step] 50

les at Target

NAME #NAME #NAME #NAME #NAME #NAME

? ? ? ? ? ?
Upper Limts
1. List the different types of arrays you can have in a worksheet.

2. In your own words, define array formula.

3. What is the difference between a SUM function that contains a direct array multiplication operation, the SUMPRODUC

4. What is the difference between a distinct set of items and a unique set of items?

5. What does the SEQUENCE array function do?

6. What does the FREQUENCY array function do?

ation, the SUMPRODUCT function, and the MMULT array function?
1. List the different types of arrays you can have in a worksheet.

The different types of arrays in the worksheet are: 1) references (ranges, defined names, tables names), 2) array constants, 3
2. In your own words, define array formula.

An worksheet array formula is a formula that contains an array operation that results in more than one item. This array oper
formula. Or the formula can have as its final operation, an array operation that results in a spilled result into the worksheet, c
array function.
3. What is the difference between a SUM function that contains a direct array multiplication operation, the SUMPRODUC

SUM function that contains a direct array multiplication operation will aggregate the values from the product operation, but
products and will avoid text value induced errors and tends to calculate more quickly than the SUM function with a direct mu
array are equal to the number of rows in the second array. Matrix multiplication will take each row in the first times each row
second".
4. What is the difference between a distinct set of items and a unique set of items?

A distinct set of items is a list of items that appear only one time in a list. A unique set of items is a list of items where all dup
5. What does the SEQUENCE array function do?

SEQUENCE array function creates an array of numbers based on: 1) the number of rows in the final array, 2) the number of c
{1;2;3;4;5;6;7}, {1;1;1;1;1;1;1}, {-1;0;1;2;3;4;5}, {1,2;3,4;5,6}, {2,4,6,8,10,12}
6. What does the FREQUENCY array function do?

The FREQUENCY array function counts numbers from a data set to determine the frequency within categories based on lowe
categories that contain an upper and lower limit for each. You must give frequency the upper limits for the categories, and th
are based on the upper limit (included) and a lower limit (the upper limit from the previous category), where the lower limit
the last category catches everything greater than the last upper limit.
es), 2) array constants, 3) resultant arrays (result of an array operation), 4) spilled arrays using the spilled range operator #.

ne item. This array operation can be internal in the formula and then used in a final aggregate operation, called a scalar array
ult into the worksheet, called a spilled array formula. The operations possible are: math, join, comparative, function argument or

ation, the SUMPRODUCT function, and the MMULT array function?

e product operation, but will error if there are text values in the array. When you use the SUMPRODUCT function, it will sum the
unction with a direct multiplication operation. The MMULT function performs matrix multiplication if the # of columns of the first
n the first times each row in the second and then add, delivering a final array with the dimension "rows from first X columns in the

t of items where all duplicates are removed.

rray, 2) the number of columns in the final array, 3) a starting number, and 4) a step value or increment value. Examples are;

ategories based on lower and upper limits. Or, said a different way: The FREQUENCY array function counts numbers based on a set of
or the categories, and then it creates the first category that is equal to or less than the first upper limit, then the middle categories
, where the lower limit is not included. In math notation it would be: Lower Limit Previous Category < Number <= Upper Limit. Then
operator #.

a scalar array
ction argument or

n, it will sum the

olumns of the first
t X columns in the

xamples are;

bers based on a set of

e middle categories
<= Upper Limit. Then