Product Sales Unique List Products:

Quad 326.17 #NAME?

Carlota 251.57 #NAME?
Quad 374.03 #NAME?
Aspen 256.22
Aspen 171.21 Formula in cell E3:
Carlota 446.2 {=_xlfn._xlws.sort(_xlfn.unique(B3:B9))}
Quad 236.06
The range reference, B3:B9, is an array in the array formula: {=_xlfn._xlws.sort(_xlfn.unique(B3:B9))}

#NAME?
Array Syntax:
Curly Brackets house the array: { }
Comma means column ,
Semi-colon means row ;
Array constant of column values: 1 2 3
D7: {={1,2,3}}
Array constant of row values: 1
D9: {={1;2;3}} 2
3
Array constant of table values: 1 Jan
D13: {={1,"Jan";2,"Feb";3,"Mar"}} 2 Feb
3 Mar
Array constant = hard coded array of column values, row values or table values.
Array constant = hard coded array of column values, row values or table values.
Thrower Time1 Time2 Time3 Time4 Time5 Total of Top 3
Stevie 143s 78s 43s 169s 118s 430s
Formula in cell H3:
{=SUM(LARGE(C3:G3,{1,2,3}))}

#NAME?
Thrower Time1 Time2 Time3 Time4 Time5 Total of Top 3
Stevie 143s 78s 43s 169s 118s 430s
Formula in cell H3:
{=SUM(LARGE(C3:G3,{1,2,3}))} = SUM({169,143,118})

The formula element, {=LARGE(C3:G3,{1,2,3})}, evaluates to the resultant array {169,143,118}

LARGE values:
169 143 118
Quad Cost Aspen Cost Tax Rate
10 8 0.1
Quad Cost*2 Aspen Cost*2 Tax Amount
20 16 #NAME?
Formula in cell B6: Formula in cell D6:
{=B3:C3*2} {=SUM(_xlfn.anchorarray(B6)*D3)}
#NAME?

Math Operator:
Quad Cost*2 Aspen Cost*2
20 16

Join Operator:
Quad Cost&2 Aspen Cost&2
102 82

Comparative Operator:
Quad Cost>=2 Aspen Cost>=2
1 1
Quad Cost Aspen Cost
10 8

Math Operator: Join Operator: Comparative Operator:

Quad Cost*2 Aspen Cost*2 Quad Cost&2 Aspen Cost&2 Quad Cost>=2 Aspen Cost>=2
20 16 102 82 1 1
Formula in cell B7: Formula in cell E7: Formula in cell H7:
{=B3:C3*2} {=B3:C3&2} {=B3:C3>=2}
Function Argument Array Operations:
Item in List 2,
Employee Gross Pay (Tax ($) List 1 List 2 also in List 1?
Chantel 2,379.09 249.8 Gigi Chantel #NAME?
Kamala 2,630.57 276.21 Ty Gigi #NAME?
Sioux 1,884.75 197.9 Sioux Ty #NAME?
Miki #NAME?
Tax Rate 10.5%
Formula in D5: Formula in I5:
{=ROUND(C5:C7*D9,2)} {=_xlfn.xmatch(H5:H8,F5:F7)}

#NAME?
{=_xlfn.xmatch(H5:H8,F5:F7)} = {#N/A;1;2;#N/A}
Array Function Operations:
Product Sales Unique List Transpose:
Quad 326.17 #NAME? #NAME? #NAME? #NAME?
Carlota 251.57 #NAME?
Quad 374.03 #NAME? Formula in G5:
Aspen 256.22 {=TRANSPOSE(_xlfn.anchorarray(E5))}
Aspen 171.21
Carlota 446.2 Sequence:
Quad 236.06 #NAME?
#NAME?
Formula in E5: #NAME?
{=_xlfn.unique(B5:B11)}
Formula in G11:
{=_xlfn.sequence(3)}

{=_xlfn.unique(B5:B11)} = {#N/A;1;2;#N/A}
#NAME?
#NAME?
Product Sales Product Formula in E3:
Quad 326.17 #NAME? {=_xlfn._xlws.sort(_xlfn.uniqu
Carlota 251.57 #NAME?
Quad 374.03 #NAME?
Aspen 256.22
Aspen 171.21
Carlota 446.2
Quad 236.06
xlws.sort(_xlfn.unique(B3:B9))}
Product Sales Product Formula in E3:
Quad 326.17 #NAME? {=_xlfn._xlws.sort(_xlfn.uniqu
Carlota 251.57
Quad 374.03 data
Aspen 256.22
Aspen 171.21
Carlota 446.2
Quad 236.06
xlws.sort(_xlfn.unique(B3:B9))}
Product Sales Product Total Sales Formula in E3:
Quad 326.17 #NAME? #NAME? {=_xlfn._xlws.sort(_xlfn.uniqu
Carlota 251.57 #NAME? #NAME?
Quad 374.03 #NAME? #NAME?
Aspen 256.22
Aspen 171.21
Carlota 446.2
Quad 236.06
xlws.sort(_xlfn.unique(B3:B9))}
Product Sales Product Total Sales Formula in E3:
Quad 326.17 #NAME? #NAME? {=_xlfn._xlws.sort(_xlfn.uniqu
Carlota 251.57 #NAME? #NAME?
Quad 374.03 #NAME? #NAME?
Aspen 256.22 #NAME? #NAME?
Yanaki 171.21
Carlota 446.2
Quad 236.06
xlws.sort(_xlfn.unique(B3:B9))}
Date Stock Open ($) Close ($) Change
10/27/2020 GOOGL 1,588.93 1,598.88 9.95
10/26/2020 GOOGL 1,615.00 1,584.29 -30.71
10/23/2020 GOOGL 1,615.05 1,632.98 17.93

Formula in cell F3: =E3-D3

Max Change: 17.93 Formula in cell F14: =MAX(F3:F5)

Date Stock Open ($) Close ($) Change
10/27/2020 GOOGL 1,588.93 1,598.88 9.95
10/26/2020 GOOGL 1,615.00 1,584.29 -30.71
10/23/2020 GOOGL 1,615.05 1,632.98 17.93

Formula in cell F3: {=E3:E5-D3:D5}

 Sales Tax Paid
10 20
20 40

Multiple
2

Price ($) Units Sales

2 5 10
5 4 20

Price ($) Units Sales

2 5 10
5 4 20
2 #N/A
Array Formula Process for Cell * Column:
Formula in cell D3: {=D7*C3:C4}
2*{10;20}
{2*10;2*20}
#NAME?
#NAME?
=Cell (1R x 1C) * Column (2R x 1C) = Column (2R x 1C)
Array Formula Process for Column * Column (same number of rows):
Formula in cell D11: {=B11:B12*C11:C12}
{2;5}*{5;4}
{2*5;5*4}
#NAME?
#NAME?
=Column (2R x 1C) * Column (2R x 1C) = Column (2R x 1C)
Array Formula Process for Column * Column (different number of rows):
Formula in cell D19: {=B19:B20*C19:C21}
{2;5}*{5;4;2}
{2*5;5*4;???*4}
{10;20;#N/A}
={2;5}*{5;4;2} = {2*5;5*4;???*4} = {10;20;#N/A}
=Column (2R x 1C) * Column (3R x 1C) = Error because columns do not contain same number of rows.
Formula in cell D3: {=D7*C3:C4
#NAME?
=Cell (1R x 1C) * Column (2R x

Formula in cell D11: {=B11:B12

#NAME?
=Column (2R x 1C) * Column (2

Formula in cell D19: {=B19:B20

={2;5}*{5;4;2} = {2*5;5*4;???*
=Column (2R x 1C) * Column (3
Formula in cell D3: {=D7*C3:C4}

=Cell (1R x 1C) * Column (2R x 1C) = Column (2R x 1C)

Formula in cell D11: {=B11:B12*C11:C12}

=Column (2R x 1C) * Column (2R x 1C) = Column (2R x 1C)

Formula in cell D19: {=B19:B20*C19:C21}

={2;5}*{5;4;2} = {2*5;5*4;???*4} = {10;20;#N/A}
=Column (2R x 1C) * Column (3R x 1C) = Error because columns do not contain same number of rows.
Sales 10 20
Tax Paid 20 40

Multiple 2

Price ($) 2 5
Units 5 4
Sales 10 20

Price ($) 2 5
Units 5 4 2
Sales 10 20 #N/A
Array Formula Process for Cell * Row:
Formula in cell C3: {=D5*C2:D2}
2*{10,20}
{2*10,2*20}
#NAME?
#NAME?
=Cell (1R x 1C) * Row (1R x 2C) = Row (1R x 2C)
Array Formula Process for Row * Row (same number of columns):
Formula in cell C12: {=C10:D10*C11:D11}
{2,5}*{5,4}
{2*5,5*4}
#NAME?
#NAME?
=Row (1R x 2C) * Row (1R x 2C) = Row (1R x 2C)
Array Formula Process for Row * Row (different number of columns):
Formula in cell C20: {=C18:D18*C19:E19}
{2,5}*{5,4,2}
{2*5;5*4;???*4}
{10,20,#N/A}
={2,5}*{5,4,2} = {2*5;5*4;???*4} = {10,20,#N/A}
=Row (1R x 2C) * Row (1R x 3C) = Error because rows do not contain same number of columns
Formula in cell C3: {=D5*C2:D2}
#NAME?
=Cell (1R x 1C) * Row (1R x 2C) = Row

Formula in cell C12: {=C10:D10*C11:

#NAME?
=Row (1R x 2C) * Row (1R x 2C) = Ro

Formula in cell C20: {=C18:D18*C19:

={2,5}*{5,4,2} = {2*5;5*4;???*4} = {1
=Row (1R x 2C) * Row (1R x 3C) = Err
a in cell C3: {=D5*C2:D2}

R x 1C) * Row (1R x 2C) = Row (1R x 2C)

a in cell C12: {=C10:D10*C11:D11}

R x 2C) * Row (1R x 2C) = Row (1R x 2C)

a in cell C20: {=C18:D18*C19:E19}

5,4,2} = {2*5;5*4;???*4} = {10,20,#N/A}
R x 2C) * Row (1R x 3C) = Error because rows do not contain same number of columns
 10 20
2 20 40
1 10 20
3 30 60

2 1 3
10 20 10 30
20 40 20 60

14 10
4 15
3 24

2 5
4 3
1 6

7 2
1 5
3 4
Array Formula Process for Column * Row:
Formula in cell C3: {=B3:B5*C2:D2}
{2;1;3}*{10,20}
{2*10,2*20;1*10,1*20;3*10,3*20}
{20,40;10,20;30,60}
={2;1;3}*{10,20} = {2*10,2*20;1*10,1*20;3*10,3*20} = {20,40;10,20;30,60}
=Column (3R x 1C) * Row (1R x 2C) = Table (3R x 2C)
Array Formula Process for Row * Column:
Formula in cell C11: {=C10:E10*B11:B12}
{2,1,3}*{10;20}
{2*10,1*10,3*10;2*20,1*20,3*20}
{20,10,30;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}
=Column (3R x 1C) * Row (1R x 2C) = Table (3R x 2C)
Array Formula Process for Table * Table:
Formula in cell B18: {=B22:C24*B26:C28}
{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}
={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}
Table (3R x 2C) * Table (3R x 2C) = Table (3R x 2C)
Formula in cell C3: {=B3:B5*C2:D2}
={2;1;3}*{10,20} = {2*10,2*20;1*10,1*20;3*10,3*20} = {20,40;10
=Column (3R x 1C) * Row (1R x 2C) = Table (3R x 2C)

Formula in cell C11: {=C10:E10*B11:B12}

={2,1,3}*{10;20} = {2*10,1*10,3*10;2*20,1*20,3*20} = {20,10,30;
=Column (3R x 1C) * Row (1R x 2C) = Table (3R x 2C)

Formula in cell B18: {=B22:C24*B26:C28}

={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;
Table (3R x 2C) * Table (3R x 2C) = Table (3R x 2C)
 *20;3*10,3*20} = {20,40;10,20;30,60}
Table (3R x 2C)

*20,1*20,3*20} = {20,10,30;40,20,60}
Table (3R x 2C)

2;4*1,3*5;1*3,6*4} = {14,10;4,15;3,24}
ble (3R x 2C)
column element from
the row: 1 4
1 1 3
row
element 2 5 1
from the
column: 1 1 2
Array Formula Process for Column * Row * Table:
1 12 {=C3:C5*D2:E2*D3:E5}
10 8 {1;2;1}*{1,4}*{1,3;5,1;1,2}
1 8 {1*1,1*4;2*1,2*4;1*1,1*4}*{1,3;5,1;1,2}
{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}
={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,4;2,8;1,4}*{1,3;5,1;1
=Column (3R x 1C) * Row (1R x 2C) * Table (3R x 2C) = Table (3R x 2C)

{=C3:C5*D2:E2*D3:E5}
={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,4;2,8;1,4}*{1,3;5,1;1
=Column (3R x 1C) * Row (1R x 2C) * Table (3R x 2C) = Table (3R x 2C)
Array Formula Process for Column * Row * Table:
{=C3:C5*D2:E2*D3:E5}
{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,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}
={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,4;2,8;1,4}*{1,3;5,1;1,2} = {1*1,4*3;2*5,8*1;1*1,4*2] = {1,1
=Column (3R x 1C) * Row (1R x 2C) * Table (3R x 2C) = Table (3R x 2C)

{=C3:C5*D2:E2*D3:E5}
={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,4;2,8;1,4}*{1,3;5,1;1,2} = {1*1,4*3;2*5,8*1;1*1,4*2] = {1,1
=Column (3R x 1C) * Row (1R x 2C) * Table (3R x 2C) = Table (3R x 2C)
 Total Sales
Product SalesRep Sales Product/SalesRep #NAME? #NAME?
Quad Sioux 10 #NAME? #NAME? #NAME?
Aspen Ty 20 #NAME? #NAME? #NAME?
Quad Ty 20
Aspen Ty 20
Aspen Sioux 20 Contains Criteria Contains?
Quad Sioux 10 Carlota #VALUE!
Quad #VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
Formula in cell G4: {=SUMIFS(D4:D9,B4:B9,F4:F5,C4:C9,G3:H3)}
Two function argument array operations = E4:E5, Column (2R x 1C), and F3:G3, Row (1R x 2C) = Table (2R x 2C) delivered by
#NAME?

Formula in cell H9: {=SEARCH(F9:F10,B4:B9)}

Two function argument array operations = E9:E10, Column (2R x 1C), and A4:A9, Column (6R x 1C) = Error because columns

=SUMIFS(D4:D9,B4:B9,F4:F5,C4:C9,G3:H3)}
Two function argument array operations = E4:E5, Column (2R x 1C), and F3:G3, Row (1R x 2C) = Table (2R x 2C) delivered by
#NAME?

=SEARCH(F9:F10,B4:B9)}
Two function argument array operations = E9:E10, Column (2R x 1C), and A4:A9, Column (6R x 1C) = Error because columns
and F3:G3, Row (1R x 2C) = Table (2R x 2C) delivered by SUMIFS.

and A4:A9, Column (6R x 1C) = Error because columns do not contain same number of rows

and F3:G3, Row (1R x 2C) = Table (2R x 2C) delivered by SUMIFS.

and A4:A9, Column (6R x 1C) = Error because columns do not contain same number of rows
Ex #1 Goal: Estimate returns for stock A & B in a portfolio of stocks.

Weight of Stock in
Portfolio: 0.6 0.4

Stock A Full Stock B Full Stock A Stock B

Probability of Estimated Estimated Contribution to Contribution to
Economic State Return Return Portfolio Portfolio
Bad 0.5 0.0 -0.15
OK 0.4 0.05 0.05
Great 0.1 0.1 0.2
Formula in cell G6:

Spills down and to left from cell G6

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

Weight of Stock in
Portfolio: 0.6 0.4

Stock A Full Stock B Full Stock A Stock B

Probability of Estimated Estimated Contribution to Contribution to
Economic State Return Return Portfolio Portfolio
Bad 0.5 0.0 -0.15 0 -0.03
OK 0.4 0.05 0.05 0.012 0.008
Great 0.1 0.1 0.2 0.006 0.008
Formula in cell G6:
{=C6:C8*D4:E4*D6:E8}

Spills down and to left from cell G6

Ex #2 Goal: Estimate returns for stock A & B in a portfolio of stocks.

Weight of Stock in
Portfolio: 0.6 0.4

Stock A Full Stock B Full Stock A Stock B

Probability of Estimated Estimated Contribution to Contribution to
Economic State Return Return Portfolio Portfolio
Bad 0.5 0.0 -0.15
OK 0.4 0.05 0.05
Great 0.1 0.1 0.2
Formula in cell G6:
Ex #2 Goal: Estimate returns for stock A & B in a portfolio of stocks.

Weight of Stock in
Portfolio: 0.6 0.4

Stock A Full Stock B Full Stock A Stock B

Probability of Estimated Estimated Contribution to Contribution to
Economic State Return Return Portfolio Portfolio
Bad 0.5 0.0 -0.15 0 -0.03
OK 0.4 0.05 0.05 0.012 0.008
Great 0.1 0.1 0.2 #N/A #N/A
Formula in cell G6:
{=C6:C7*D4:E4*D6:E8}
Ex #3 Goal: Estimate returns for stock A & B in a portfolio of stocks.

Weight of Stock in
Portfolio: 0.6 0.4

Stock A Full Stock B Full Stock A Stock B

Probability of Estimated Estimated Contribution to Contribution to
Economic State Return Return Portfolio Portfolio
Bad 0.5 0.0 -0.15
OK 0.4 0.05 0.05
Great 0.1 0.1 0.2
Formula in cell G6:

Entered into cell G6 and copied down and to left.

Ex #3 Goal: Estimate returns for stock A & B in a portfolio of stocks.

Weight of Stock in
Portfolio: 0.6 0.4

Stock A Full Stock B Full Stock A Stock B

Probability of Estimated Estimated Contribution to Contribution to
Economic State Return Return Portfolio Portfolio
Bad 0.5 0.0 -0.15 0 -0.03
OK 0.4 0.05 0.05 0.012 0.008
Great 0.1 0.1 0.2 0.006 0.008
Formula in cell G6:
=$C6*D6*D$4

Entered into cell G6 and copied down and to left.

Ex #4 Goal: Calculate the expected portfolio of stocks return.

Weight of Stock in
Portfolio: 0.6 0.4

Stock A Full Stock B Full Stock A Stock B

Probability of Estimated Estimated Contribution to Contribution to
Economic State Return Return Portfolio Portfolio
Bad 0.5 0.0 -0.15 0 -0.03
OK 0.4 0.05 0.05 0.012 0.008
Great 0.1 0.1 0.2 0.006 0.008
Expected Portfolio Returns: 2-Step Formula: #NAME?
Formula in cell E10: Formula in cell G6:
{=C6:C8*D4:E4*D6:E8}
Formula in cell H10:
=SUM(_xlfn.anchorarray(G6))
Ex #4 Goal: Calculate the expected portfolio of stocks return.

Weight of Stock in
Portfolio: 0.6 0.4

Stock A Full Stock B Full Stock A Stock B

Probability of Estimated Estimated Contribution to Contribution to
Economic State Return Return Portfolio Portfolio
Bad 0.5 0.0 -0.15 0 -0.03
OK 0.4 0.05 0.05 0.012 0.008
Great 0.1 0.1 0.2 0.006 0.008
Expected Portfolio Returns: 0.004 2-Step Formula: #NAME?
Formula in cell E10: Formula in cell G6:
{=SUM(C6:C8*D4:E4*D6:E8)} {=C6:C8*D4:E4*D6:E8}
Formula in cell H10:
=SUM(_xlfn.anchorarray(G6))
Ex #5 Goal: Calculate total sales (price * units) in single cell.

Product Price Units Total Sales ($)

c 49.05 6
q 13.31 126
a 26.79 1 Formula in cell F5:
c 37.92 167
c 46.75 10
 Time in milliseconds:

Total Sales ($) SUM Times:

7.3
7.3
Formula in cell H5: 7.5
7.4
Ave: 7.38

% Decrease:
in milliseconds:

SUMPRODUCT times:
6.6
6.5
6.6
6.6
6.57

-10.85%
Ex #5 Goal: Calculate total sales (price * units) in single cell.

Product Price Units Total Sales ($)

c 49.05 6 284,443,532.22
q 13.31 126
a 26.79 1 Formula in cell F5:
c 37.92 167 {=SUM(C5:C100556*D5:D100556)}
c 46.75 10
 Time in milliseconds:

Total Sales ($) SUM Times:

284,443,532.22 7.3
7.3
Formula in cell H5: 7.5
=SUMPRODUCT(C5:C100556,D5:D100556) 7.4
Ave: 7.38

% Decrease:
in milliseconds:

SUMPRODUCT times:
6.6
6.5
6.6
6.6
6.57

-10.85%
Ex #6 Goal: Calculate total sales (price * units) in single cell, have formula ignore text.

Product Price Units Total Sales ($) Total Sales ($)

Carlota 49.05 9
Quad 13.31 7
Aspen 26.79 10 Formula in cell F5: Formula in cell H5:
Aspen 37.92 2
Yanaki NA 2
Carlota 40.37 5
Quad 25.83 7
Aspen 44.77 6
Ex #6 Goal: Calculate total sales (price * units) in single cell, have formula ignore text.

Product Price Units Total Sales ($) Total Sales ($)

Carlota 49.05 9 #VALUE! 1,529.64
Quad 13.31 7
Aspen 26.79 10 Formula in cell F5: Formula in cell H5:
Aspen 37.92 2 {=SUM(C5:C12*D5:D12)} =SUMPRODUCT(C5:C12,D5:D12)
Yanaki NA 2
Carlota 40.37 5
Quad 25.83 7
Aspen 44.77 6
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#7 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
Weights: 20% 25% 15% 40%
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
Sioux 91 94 91 87.5
Chin 98 87.5 79 86
Ty 73 36 56 78
Mo 65 70 72 84

Longhand formula: =C8*$C$5+D8*$D$5+E8*$E$5+F8*$F$5 = 90.35

Too much work: 91*20% + 94*25.00% + 91*15.00% + 87.5*40.00% = 18.2 + 23.5 + 13.65 + 35 = 90.35
.5 + 13.65 + 35 = 90.35

Weighted Average
90.35
87.73
63.20
74.90

.5 + 13.65 + 35 = 90.35
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#7 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
Weights: 20% 25% 15% 40%
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
Sioux 91 94 91 87.5 90.35
Chin 98 87.5 79 86 87.73
Ty 73 36 56 78 63.20
Mo 65 70 72 84 74.90
Formula in cell G8: =SUMPRODUCT(C8:F8,$C$5:$F$5)

Longhand formula: =C8*$C$5+D8*$D$5+E8*$E$5+F8*$F$5 = 90.35

Too much work: 91*20% + 94*25.00% + 91*15.00% + 87.5*40.00% = 18.2 + 23.5 + 13.65 + 35 = 90.35
.5 + 13.65 + 35 = 90.35

Weighted Average
90.35
87.73
63.20
74.90
F8,$C$5:$F$5)

.5 + 13.65 + 35 = 90.35
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#8 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
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 20%
Test 2 25%
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#8 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
Sioux 91 94 91 87.5 #VALUE!
Chin 98 87.5 79 86 #VALUE!
Ty 73 36 56 78 #VALUE!
Mo 65 70 72 84 #VALUE!
Test Weights Formula in cell G6: =SUMPRODUCT(C6:F6,$C$12:$C$15)
Test 1 20%
Test 2 25%
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35

$12:$C$15)
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#9 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
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 20%
Test 2 25%
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#9 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
Sioux 91 94 91 87.5 363.50
Chin 98 87.5 79 86 280.40
Ty 73 36 56 78 133.65
Mo 65 70 72 84 116.40
Test Weights Formula in cell G6: {=SUM(C6:F6*C12:C15)}
Test 1 20%
Test 2 25%
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#10 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
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 20%
Test 2 25% Then manually copy down ê
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#10 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
Sioux 91 94 91 87.5 90.35
Chin 98 87.5 79 86 87.73
Ty 73 36 56 78 63.20
Mo 65 70 72 84 74.90
Test Weights Formula in cell G6: =C6*$C$12+D6*$C$13+E6*$C$14+F6*$C$15
Test 1 20%
Test 2 25% Then manually copy down ê
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35

6*$C$14+F6*$C$15
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#11 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
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 20%
Test 2 25% Spills down ê
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#11 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
Sioux 91 94 91 87.5 90.35
Chin 98 87.5 79 86 87.73
Ty 73 36 56 78 63.20
Mo 65 70 72 84 74.90
Test Weights Formula in cell G6: {=C6:C9*C12+D6:D9*C13+E6:E9*C14+F6:F9*C15}
Test 1 20%
Test 2 25% Spills down ê
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35

+E6:E9*C14+F6:F9*C15}
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#12 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
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 20%
Test 2 25% Spills down ê
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35
Ex Goal: Multiply each test score by each test weight and then add to get student total score.
#12 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
Sioux 91 94 91 87.5 90.35
Chin 98 87.5 79 86 87.73
Ty 73 36 56 78 63.20
Mo 65 70 72 84 74.90
Test Weights Formula in cell G6: {=MMULT(C6:F9,C12:C15)}
Test 1 20%
Test 2 25% Spills down ê
Test 3 15%
Test 4 40%
Actual Numbers
#NAME? Ave: W. Ave:
#NAME? ### 90.35
#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?
23.5 + 13.65 + 35 = 90.35
1) No empty cells in either array ü
91 94 91 87.5 Array 1: 4R x 4C
98 87.5 79 86
73 36 56 78
65 70 72 84
2) Number of columns in array 1 = number of rows in array 2 ü
1 2 3 4 Array 1:
▼ ▼ ▼ ▼ R x
91 94 91 87.5 4 x
98 87.5 79 86
73 36 56 78
65 70 72 84
3) Goal is to multiply each row in array 1 times each column in array 2
Column 1
Row 1 91 94 91 87.5 x 20%
Row 2 98 87.5 79 86 25%
Row 3 73 36 56 78 15%
Row 4 65 70 72 84 40%

4) Dimensions of desired spilled result = number rows in array 1 x nu

Array 1: Array 2:
R x C R x C
4 x 4 4 x 1

Spilled array dimensions = 4 x 1

4R x 1C
90.35
87.725
63.2
74.9
 Array 1: 4R x 4C 20% Array 2: 4R x 1 C
25%
15%
40%
er of rows in array 2 ü
Array 2:
C R x C
4 4 x 1 20% ◄ 1
25% ◄ 2
15% ◄ 3
Must be equal 40% ◄ 4

times each column in array 2, then add ü

Multiply:
Row 1*Column 1 = {91,94,91,87.5}*{20%;25%;15%;40%}
Row 2*Column 1 = {98,87.5,79,86}*{20%;25%;15%;40%}
Row 3*Column 1 = {73,36,56,78}*{20%;25%;15%;40%}
Row 4*Column 1 = {65,70,72,84}*{20%;25%;15%;40%}
then Add:
91*20% + 94*25% + 91*15% + 87.5*40% = 90.35
98*20% + 87.5*25% + 79*15% + 86*40% = 87.725
73*20% + 36*25% + 56*15% + 78*40% = 63.2
65*20% + 70*25% + 72*15% + 84*40% = 74.9
number rows in array 1 x number of columns in array 2 ü
Ex Goal: Calculate Highline College athletic department baseball order total costs for each team.
#13 3 columns in array 1 and 3 rows in array 2, you can use MMULT.

Costs: Bats Balls Gloves

$27 $3 $37

Women's Team Men's Team

Items: Number Units Ordered:
Bats 13 16
Balls 43 69
Gloves 19 14
Totals:

Spills to right è
Ex Goal: Calculate Highline College athletic department baseball order total costs for each team.
#13 3 columns in array 1 and 3 rows in array 2, you can use MMULT.

Costs: Bats Balls Gloves

$27 $3 $37

Women's Team Men's Team

Items: Number Units Ordered:
Bats 13 16
Balls 43 69
Gloves 19 14
Totals: 1183 1157
Formula in cell C13: {=MMULT(C6:E6,C10:D12)}
Spills to right è
1) No empty cells in either array ü
$27 $3 $37 Array 1: 1R x 3C

2) Number of columns in array 1 = number of rows in array 2 ü

1 2 3 Array 1:
▼ ▼ ▼ R x C
$27 $3 $37 1 x 3

Must be equal
3) Goal is to multiply each row in array 1 times each column in array 2
Column 1 Column 2
Row 1 $27 $3 $37 x 13 16
43 69
19 14

4) Desired spilled result dimensions = number rows in array 1 x numb

Array 1: Array 2:
R x C R x C
1 x 3 3 x 2

Spilled array dimensions = 4 x 1

1R x 2C
1183 1157
 13 16 Array 2: 3R x 2 C
43 69
19 14
r of rows in array 2 ü
Array 2:
R x C
3 x 2 13 16 ◄ 1
43 69 ◄ 2
19 14 ◄ 3
Must be equal
times each column in array 2, then add ü
Multiply:
Row 1*Column 1 = {27,3,37}*{13;43;19}
Row 1*Column 2 = {27,3,37}*{16;69;14}
then Add:
27*13 + 3*43 + 37*19 = 1183
27*16 + 3*69 + 37*14 = 1157
ber rows in array 1 x number of columns in array 2 ü
Ex Goal: Calculate Highline College athletic department baseball order total costs for each team.
#14 Parallel arrays, then you can use SUMPRODUCT, or TRANSPOSE and MMULT.
Women's Team Men's Team
Items: Costs: Number Units Ordered:
Bats $27 13 16
Balls $3 43 69
Gloves $37 19 14
Totals:

Then manually copy to right è

OR
Totals:

Spills to right è
Ex Goal: Calculate Highline College athletic department baseball order total costs for each team.
#14 Parallel arrays, then you can use SUMPRODUCT, or TRANSPOSE and MMULT.
Women's Team Men's Team
Items: Costs: Number Units Ordered:
Bats $27 13 16
Balls $3 43 69
Gloves $37 19 14
Totals: 1183 1157
Formula in cell D10: =SUMPRODUCT(D7:D9,$C$7:$C$9)
Then manually copy to right è
OR
Totals: 1183 1157
Formula in cell D15: {=MMULT(TRANSPOSE(C7:C9),D7:E9)}
Spills to right è
Name Test 1 Test 2 Test 3 Test 4 Total Test Points
Sioux 91 94 91 87.5
Chin 98 87.5 79 86
Ty 73 36 56 78
Mo 65 70 72 84

Then manually copy down ê

Name Test 1 Test 2 Test 3 Test 4 Total Test Points
Sioux 91 94 91 87.5 363.50 ◄ =SUM(C5:F5)
Chin 98 87.5 79 86 350.50 ◄ =SUM(C6:F6)
Ty 73 36 56 78 243.00 ◄ =SUM(C7:F7)
Mo 65 70 72 84 291.00 ◄ =SUM(C8:F8)

Formula in cell G5: =SUM(C5:F5)

Then manually copy down ê

Name Test 1 Test 2 Test 3 Test 4 Total Test Points
Sioux 91 94 91 87.5
Chin 98 87.5 79 86
Ty 73 36 56 78
Mo 65 70 72 84

Then manually copy down ê

Name Test 1 Test 2 Test 3 Test 4 Total Test Points
Sioux 91 94 91 87.5 1248.00 ◄ =SUM(C5:F8)
Chin 98 87.5 79 86
Ty 73 36 56 78
Mo 65 70 72 84

Formula in cell G5: =SUM(C5:F8)

Then manually copy down ê

Ex Goal: Create a formula that can spill weighted average.
#12 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
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 20%
Test 2 25% Spills down ê
Test 3 15%
Test 4 40%
5% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Ex Goal: Create a formula that can spill weighted average.
#12 Example: Sioux's Total Score = 91*20% + 94*25% + 91*15% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35
Name Test 1 Test 2 Test 3 Test 4 Weighted Average
Sioux 91 94 91 87.5 90.35
Chin 98 87.5 79 86 87.73
Ty 73 36 56 78 63.20
Mo 65 70 72 84 74.90

Test Weights Formula in cell G6: {=MMULT(C6:F9,C12:C15)}

Test 1 20%
Test 2 25% Spills down ê
Test 3 15%
Test 4 40%
5% + 87.5*40% = 18.2 + 23.5 + 13.65 + 35 = 90.35

MULT(C6:F9,C12:C15)}
Ex Goal: Create a formula that can spill row totals for each row in table.
#15 Example: Sioux's Total Score = 91*1 + 94*1 + 91*1 + 87.5*1 = 91 + 94 + 91 + 87.5 = 363.5
Name Test 1 Test 2 Test 3 Test 4 Total Test Points
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 1
Test 2 1 Spills down ê
Test 3 1
Test 4 1
Ex Goal: Create a formula that can spill row totals for each row in table.
#15 Example: Sioux's Total Score = 91*1 + 94*1 + 91*1 + 87.5*1 = 91 + 94 + 91 + 87.5 = 363.5
Name Test 1 Test 2 Test 3 Test 4 Total Test Points
Sioux 91 94 91 87.5 363.50
Chin 98 87.5 79 86 350.50
Ty 73 36 56 78 243.00
Mo 65 70 72 84 291.00

Test Weights Formula in cell G6: {=MMULT(C6:F9,C12:C15)}

Test 1 1
Test 2 1 Spills down ê
Test 3 1
Test 4 1
 Name Test 1 Test 2 Test 3 Test 4 TotalPoints
Array 1: 4R x 4C Sioux 91 94 91 87.5 363.50 Spilled Array: 4R x 1 C
Chin 98 87.5 79 86 350.50
Ty 73 36 56 78 243.00
Mo 65 70 72 84 291.00

Array 1: Array 2:
R x C R x C
4 x 4 ? x ?

Must be equal

Spilled array dimensions = 4 x 1

Therefore, Array 2 must be: 4R x 1 C

1
1
1
1
###
###
###
###
Ex Goal: Create single cell formulas to calculate:
#16 Expected portfolio of stocks return.
Standard deviation of expected portfolio returns.

Weight of Stock in Portfolio: 0.6 0.4

Stock A Full Stock B Full

Probability of Estimated Estimated
Economic State Return Return
Bad 0.5 0.0 -0.15
OK 0.4 0.05 0.05
Great 0.1 0.1 0.2
Expected Portfolio Returns:
Standard Deviation of Ex. Portfolio Returns:
Standard Deviation of Ex. Portfolio Returns:
Standard Deviation of Ex. Portfolio Returns:
Ex Goal: Create single cell formulas to calculate:
#16 Expected portfolio of stocks return.
Standard deviation of expected portfolio returns.

Weight of Stock in Portfolio: 0.6 0.4

Stock A Full Stock B Full

Probability of Estimated Estimated
Economic State Return Return
Bad 0.5 0.0 -0.15
OK 0.4 0.05 0.05
Great 0.1 0.1 0.2
Expected Portfolio Returns: 0.004
Standard Deviation of Ex. Portfolio Returns: 0.0688767014
Standard Deviation of Ex. Portfolio Returns: 0.0688767014
Standard Deviation of Ex. Portfolio Returns: #NAME?
Formula in cell E12: {=SUM(C8:C10*D6:E6*D8:E10)}
Formula in cell E13: {=SQRT(SUM((MMULT(D8:E10*D6:E6,{1;1})-E12)^2*C8:C10))}
Formula in cell E14: {=SQRT(SUM((MMULT(D8:E10*D6:E6,TRANSPOSE(COLUMN(D6:E6)^0))-E12)^2*C8:C10
Formula in cell E15: {=SQRT(SUM((MMULT(D8:E10*D6:E6,_xlfn.sequence(COLUMNS(D6:E6),1,1,0))-E12)^2
6:E6)^0))-E12)^2*C8:C10))}
NS(D6:E6),1,1,0))-E12)^2*C8:C10))}
Ex #17 Goal: Spill Row Totals using MMULT, SEQUENCE and COLUMNS functions.
Name Test 1 Test 2 Test 3 Test 4 Total Test Points
Sioux 91 94 91 87.5
Chin 98 87.5 79 86
Ty 73 36 56 78
Mo 65 70 72 84

Spills down ê
Ex #17 Goal: Spill Row Totals using MMULT, SEQUENCE and COLUMNS functions.
Name Test 1 Test 2 Test 3 Test 4 Total Test Points
Sioux 91 94 91 87.5 #NAME?
Chin 98 87.5 79 86 #NAME?
Ty 73 36 56 78 #NAME?
Mo 65 70 72 84 #NAME?
Formula in cell G5: {=MMULT(C5:F8,_xlfn.sequence(COLUMNS(C5:F8),1,1,0))}
Spills down ê
 Rows:
TRUEs and FALSEs
Product Product Product =FILTER({"Q";"C";"Q";"A"},{TRUE;FALSE;TRUE;FALSE}) = {"Q";"Q"}
Q Q #NAME? #NAME?
C #NAME? #NAME?
Q Ones and Zeroes
A =FILTER({"Q";"C";"Q";"A"},{1;0;1;0}) = {"Q";"Q"}
#NAME?
#NAME?
Numbers
=FILTER({"Q";"C";"Q";"A"},{43;0;-2;0}) = {"Q";"Q"}
#NAME?
#NAME?
Mixed
=FILTER({"Q";"C";"Q";"A"},{TRUE;0;-2;0}) = {"Q";"Q"}
#NAME?
#NAME?

Mixed with not numbers or Boolen

=FILTER({"Q";"C";"Q";"A"},{TRUE;"";-2;"Text"})
#NAME?

Columns:
=FILTER({"Q","C","Q","A"},{TRUE,FALSE,TRUE,FALSE}) = {"Q","Q"}
#NAME? #NAME?
 Not enough rows in include
UE;FALSE}) = {"Q";"Q"} =FILTER({"Q";"C";"Q";"A"},{TRUE;FALSE;TRUE}) = #VALUE!
#NAME?

Not enough columns in include

UE,FALSE}) = {"Q","Q"} =FILTER({"Q","C","Q","A"},{TRUE,FALSE,TRUE}) = #VALUE!
#NAME?
Product Color Sales Product Product Color Sales Single condition logical t
Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red 251.57 #NAME? #NAME? #NAME?
Quad Blue 374.03 #NAME? #NAME? #NAME?
Aspen Red 256.22
Aspen Blue 171.21 Formula in cell H5: {=_xlfn._xlws.filter(B5:D11,B5:B11=F5,"No matche
Carlota Red 446.2 Spills down ê
Quad Clear 236.06

Product Color Sales Product Product Color Sales AND logical test
Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red 251.57 #NAME? #NAME? #NAME?
Quad Blue 374.03 Color
Aspen Red 256.22 Blue
Aspen Blue 171.21
Carlota Red 446.2 Formula in cell H15: {=_xlfn._xlws.filter(B15:D21,(B15:B21=F15)*(C15
Quad Clear 236.06 Spills down ê

Product Color Sales Product Product Color Sales OR logical test

Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red 251.57 #NAME? #NAME? #NAME?
Quad Blue 374.03 Color #NAME? #NAME? #NAME?
Aspen Red 256.22 Blue #NAME? #NAME? #NAME?
Aspen Blue 171.21
Carlota Red 446.2 Formula in cell H25: {=_xlfn._xlws.filter(B25:D31,(B25:B31=F25)+(C25
Quad Clear 236.06 Spills down ê

Product Color Sales Product Product Color Sales NOT logical test
Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red 251.57 #NAME? #NAME? #NAME?
Quad Blue 374.03 #NAME? #NAME? #NAME?
Aspen Red 256.22 #NAME? #NAME? #NAME?
Aspen Blue 171.21
Carlota Red 446.2 Formula in cell H35: {=_xlfn._xlws.filter(B35:D41,B35:B41<>F35)}
Quad Clear 236.06 Spills down ê

Product Color Sales Product Product Color Sales array of numbers

Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red #NAME? #NAME? #NAME?
Quad Blue #NAME? #NAME? #NAME?
Aspen Red
Aspen Blue 171.21 Formula in cell H45: {=_xlfn._xlws.filter(B45:D51,D45:D51)}
Carlota Red Spills down ê
Quad Clear 236.06
 Single condition logical test

5:B11=F5,"No matches")}

AND logical test

,(B15:B21=F15)*(C15:C21=F18))}

OR logical test

,(B25:B31=F25)+(C25:C31=F28))}

NOT logical test

,B35:B41<>F35)}

array of numbers
,D45:D51)}
array argument = [if_empty] argument =
table, column or value to use if returned
row to be filtered filter is empty

FILTER(array, include, [if_empty])

include argument = Boolean logical tests that
indicates the items to keep in the filtered result
Ex #18 Goal: Extract "Quad" product records.
Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad
Carlota Red 251.57
Quad Blue 374.03
Aspen Red 256.22
Aspen Blue 171.21
Carlota Red 446.2
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,B5:B11=F5)

1. Which row in the product column is a "Quad"?
=FILTER(B5:D11,B5:B11="Quad")
2. The single condition Boolean test evaluates to TRUEs and FALSEs.
=FILTER(B5:D11,{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})
3. TRUE indicates that the row must be returned in the resultant array of answers.
Single condition logical test.
Ex #18 Goal: Extract "Quad" product records.
Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red 251.57 #NAME? #NAME? #NAME?
Quad Blue 374.03 #NAME? #NAME? #NAME?
Aspen Red 256.22
Aspen Blue 171.21
Carlota Red 446.2 Formula in cell H5: {=_xlfn._xlws.filter(B5:D11,B5:B11=F5)}
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,B5:B11=F5)

1. Which row in the product column is a "Quad"?
=FILTER(B5:D11,B5:B11="Quad")
2. The single condition Boolean test evaluates to TRUEs and FALSEs.
=FILTER(B5:D11,{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})
3. TRUE indicates that the row must be returned in the resultant array of answers.
 Single condition logical test.
:B11=F5)}
Ex Goal: Extract records that contain the product "Quad" AND the color "Blue".
#19 Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad
Carlota Red 251.57
Quad Blue 374.03 Color
Aspen Red 256.22 Blue
Aspen Blue 171.21
Carlota Red 446.2
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,(B5:B11=F5)*(C5:C11=F8),"NA")

1. Which row in the product column has a "Quad"? Which row in the color column has a "Blue"?
=FILTER(B5:D11,(B5:B11="Quad")*(C5:C11="Blue"),"NA")
2. A TRUE in the first array and a TRUE in the corresponding position in the second array indicates that
the record meets both conditions and should be included in the result.
=FILTER(B5:D11,{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})
=FILTER(B5:D11,{TRUE*TRUE;FALSE*FALSE;TRUE*TRUE;FALSE*FALSE;FALSE*TRUE;FALSE*FALSE;TRUE*FALSE})
=FILTER(B5:D11,{1;0;1;0;0;0;0})
3. Multiplication yields a one when both conditions are met.
=FILTER(B5:D11,{1;0;1;0;0;0;0})
4. 1 indicates that the row must be returned in the resultant array of answers.
 AND logical test

olor column has a "Blue"?

the second array indicates that

E;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})
E;FALSE*TRUE;FALSE*FALSE;TRUE*FALSE})
Ex Goal: Extract records that contain the product "Quad" AND the color "Blue".
#19 Product Color Sales Product Product Color Sales
Quad Blue 326.17 w #NAME?
Carlota Red 251.57
Quad Blue 374.03 Color
Aspen Red 256.22 Blue
Aspen Blue 171.21
Carlota Red 446.2 Formula in cell H5: {=_xlfn._xlws.filter(B5:D11,(B5:B11=F5)*(C5:C11=F8)
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,(B5:B11=F5)*(C5:C11=F8),"NA")

1. Which row in the product column has a "Quad"? Which row in the color column has a "Blue"?
=FILTER(B5:D11,(B5:B11="Quad")*(C5:C11="Blue"),"NA")
2. A TRUE in the first array and a TRUE in the corresponding position in the second array indicates that
the record meets both conditions and should be included in the result.
=FILTER(B5:D11,{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}*{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})
=FILTER(B5:D11,{TRUE*TRUE;FALSE*FALSE;TRUE*TRUE;FALSE*FALSE;FALSE*TRUE;FALSE*FALSE;TRUE*FALSE})
=FILTER(B5:D11,{1;0;1;0;0;0;0})
3. Multiplication yields a one when both conditions are met.
=FILTER(B5:D11,{1;0;1;0;0;0;0})
4. 1 indicates that the row must be returned in the resultant array of answers.

OR:

Product Product Color Sales

w #NAME? #NAME? #NAME?

Color
Blue

Formula in cell H28: {=_xlfn._xlws.filter(B5:D11,(B5:B11=F28)*(C5:C11=F

Spills down ê
ws.filter(B5:D11,(B5:B11=F5)*(C5:C11=F8),"NA")} AND logical test

olor column has a "Blue"?

the second array indicates that

E;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})
E;FALSE*TRUE;FALSE*FALSE;TRUE*FALSE})

xlws.filter(B5:D11,(B5:B11=F28)*(C5:C11=F31),{"None","-","-"})}
Ex Goal: Extract records that contain the product "Quad" OR the color "Blue".
#20 Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad
Carlota Red 251.57
Quad Blue 374.03 Color
Aspen Red 256.22 Blue
Aspen Blue 171.21
Carlota Red 446.2
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,(B5:B11=F5)+(C5:C11=F8))

1. Which row in the product column has a "Quad"? Which row in the color column has a "Blue"?
=FILTER(B5:D11,(B5:B11="Quad")+(C5:C11="Blue"))
2. A TRUE in the first array or a TRUE in the second array indicates that record should be included in result.
=FILTER(B5:D11,{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}+{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})
=FILTER(B5:D11,{TRUE+TRUE;FALSE+FALSE;TRUE+TRUE;FALSE+FALSE;FALSE+TRUE;FALSE+FALSE;TRUE+FALSE})
=FILTER(B5:D11,{2;0;2;0;1;0;1})
3. Addition yields a one if one of the conditions is met and a two if both conditions are met.
=FILTER(B5:D11,{2;0;2;0;1;0;1})
4. 1 or 2 indicates that the row must be returned in the resultant array of answers.
 OR logical test

olor column has a "Blue"?

record should be included in result.

E;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})
E;FALSE+TRUE;FALSE+FALSE;TRUE+FALSE})

h conditions are met.

of answers.
Ex Goal: Extract records that contain the product "Quad" OR the color "Blue".
#20 Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red 251.57 #NAME? #NAME? #NAME?
Quad Blue 374.03 Color #NAME? #NAME? #NAME?
Aspen Red 256.22 Blue #NAME? #NAME? #NAME?
Aspen Blue 171.21
Carlota Red 446.2 Formula in cell H5: {=_xlfn._xlws.filter(B5:D11,(B5:B11=F5)+(C5:C11=F8)
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,(B5:B11=F5)+(C5:C11=F8))

1. Which row in the product column has a "Quad"? Which row in the color column has a "Blue"?
=FILTER(B5:D11,(B5:B11="Quad")+(C5:C11="Blue"))
2. A TRUE in the first array or a TRUE in the second array indicates that record should be included in result.
=FILTER(B5:D11,{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}+{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})
=FILTER(B5:D11,{TRUE+TRUE;FALSE+FALSE;TRUE+TRUE;FALSE+FALSE;FALSE+TRUE;FALSE+FALSE;TRUE+FALSE})
=FILTER(B5:D11,{2;0;2;0;1;0;1})
3. Addition yields a one if one of the conditions is met and a two if both conditions are met.
=FILTER(B5:D11,{2;0;2;0;1;0;1})
4. 1 or 2 indicates that the row must be returned in the resultant array of answers.
ws.filter(B5:D11,(B5:B11=F5)+(C5:C11=F8))} OR logical test

olor column has a "Blue"?

record should be included in result.

E;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE})
E;FALSE+TRUE;FALSE+FALSE;TRUE+FALSE})

h conditions are met.

of answers.
Ex #21 Goal: Extract records where the product is NOT "Quad".
Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad
Carlota Red 251.57
Quad Blue 374.03
Aspen Red 256.22
Aspen Blue 171.21
Carlota Red 446.2
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,B5:B11<>F5)

1. Which row in the product column is NOT a "Quad"?
=FILTER(B5:D11,B5:B11<>"Quad")
2. The single condition Boolean test evaluates to TRUEs and FALSEs.
=FILTER(B5:D11,{FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE})
3. TRUE indicates that the row must be returned in the resultant array of answers.
NOT logical test.
Ex #21 Goal: Extract records where the product is NOT "Quad".
Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red 251.57 #NAME? #NAME? #NAME?
Quad Blue 374.03 #NAME? #NAME? #NAME?
Aspen Red 256.22 #NAME? #NAME? #NAME?
Aspen Blue 171.21
Carlota Red 446.2 Formula in cell H5: {=_xlfn._xlws.filter(B5:D11,B5:B11<>F5)}
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,B5:B11<>F5)

1. Which row in the product column is NOT a "Quad"?
=FILTER(B5:D11,B5:B11<>"Quad")
2. The single condition Boolean test evaluates to TRUEs and FALSEs.
=FILTER(B5:D11,{FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE})
3. TRUE indicates that the row must be returned in the resultant array of answers.
:D11,B5:B11<>F5)} NOT logical test.
Ex Goal: Filter out records where no sale is entered.
#22 Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad
Carlota Red
Quad Blue
Aspen Red
Aspen Blue 171.21
Carlota Red
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,D5:D11)

1. Which row in the sales column has a number?
=FILTER(B5:D11,{326.17;0;0;0;171.21;0;236.06})
2. Any non-zero number indicates that the row must be returned in the resultant array of answers.
Rows with non-zero numbers
Ex Goal: Filter out records where no sale is entered.
#22 Product Color Sales Product Product Color Sales
Quad Blue 326.17 Quad #NAME? #NAME? #NAME?
Carlota Red #NAME? #NAME? #NAME?
Quad Blue #NAME? #NAME? #NAME?
Aspen Red
Aspen Blue 171.21
Carlota Red Formula in cell H5: {=_xlfn._xlws.filter(B5:D11,D5:D11)}
Quad Clear 236.06 Spills down ê

Steps for calculation of =FILTER(B5:D11,D5:D11)

1. Which row in the sales column has a number?
=FILTER(B5:D11,{326.17;0;0;0;171.21;0;236.06})
2. Any non-zero number indicates that the row must be returned in the resultant array of answers.
Rows with non-zero numbers
Ex Goal: Filter data set to show only the "Product" and "Sales" columns.
#23 Sales Rep Product Color Date Sales Product Sales
Sioux Quad Blue ### 326.17
Chantel Carlota Red ### 251.57
Chin Quad Blue ### 374.03
Chantel Aspen Red ### 256.22
Chin Aspen Blue ### 171.21
Sioux Carlota Red ### 446.2
Sioux Quad Clear ### 236.06

Spills down ê

Steps for calculation of =FILTER(B5:F11,ISNUMBER(XMATCH(B4:F4,H4:I4)))

1. XMATCH looks up the table field names within range H4:I4 to determine the relative position of 1 or 2.
=FILTER(B5:F11,ISNUMBER(XMATCH({"Sales Rep","Product","Color","Date","Sales"},{"Product","Sales"})))
2. XMATCH returns #N/A when field name not found and relative position when field name is found.
=FILTER(B5:F11,ISNUMBER({#N/A,1,#N/A,#N/A,2}))
3. ISNUMBER converts numbers to TRUE and all other data types to FALSE.
=FILTER(B5:F11,{FALSE,TRUE,FALSE,FALSE,TRUE})
4. TRUE indicates that the column must be returned in the resultant array of answers.
 Product Sales
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
Filter columns Or, if you want to hard code the order of columns into for
{=_xlfn._xlws.filter(B5:F11,{0,1,0,0,1})}

rmine the relative position of 1 or 2.

","Date","Sales"},{"Product","Sales"})))
ition when field name is found.

array of answers.
er of columns into formula:
r(B5:F11,{0,1,0,0,1})}
Ex Goal: Filter data set to show only the "Product" and "Sales" columns.
#23 Sales Rep Product Color Date Sales Product Sales
Sioux Quad Blue ### 326.17 #NAME? #NAME?
Chantel Carlota Red ### 251.57 #NAME? #NAME?
Chin Quad Blue ### 374.03 #NAME? #NAME?
Chantel Aspen Red ### 256.22 #NAME? #NAME?
Chin Aspen Blue ### 171.21 #NAME? #NAME?
Sioux Carlota Red ### 446.2 #NAME? #NAME?
Sioux Quad Clear ### 236.06 #NAME? #NAME?
Formula in cell H5: {=_xlfn._xlws.filter(B5:F11,ISNUMBER(_xlfn.xmatch(B4:F4,H4:I4)))}
Spills down ê

Steps for calculation of =FILTER(B5:F11,ISNUMBER(XMATCH(B4:F4,H4:I4)))

1. XMATCH looks up the table field names within range H4:I4 to determine the relative position of 1 or 2.
=FILTER(B5:F11,ISNUMBER(XMATCH({"Sales Rep","Product","Color","Date","Sales"},{"Product","Sales"})))
2. XMATCH returns #N/A when field name not found and relative position when field name is found.
=FILTER(B5:F11,ISNUMBER({#N/A,1,#N/A,#N/A,2}))
3. ISNUMBER converts numbers to TRUE and all other data types to FALSE.
=FILTER(B5:F11,{FALSE,TRUE,FALSE,FALSE,TRUE})
4. TRUE indicates that the column must be returned in the resultant array of answers.
 Product Sales
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
_xlfn.xmatch(B4:F4,H4:I4)))} Filter columns Or, if you want to hard code the order of columns into for
{=_xlfn._xlws.filter(B5:F11,{0,1,0,0,1})}

rmine the relative position of 1 or 2.

","Date","Sales"},{"Product","Sales"})))
ition when field name is found.

array of answers.
er of columns into formula:
r(B5:F11,{0,1,0,0,1})}
 array argument = [exactly_once] argument = TRUE
table, column or row that = unique set
is the source data FALSE or omitted = distinct set

UNIQUE(array, [by_col], [exactly_once])

[by_col] argument =
TRUE = returns unique/distinct columns
FALSE or omitted = returns unique/distinct rows
ment = TRUE
et
distinct set
Ex #24 Goal: Extract a distinct list of sales rep names from a column.
Sales Rep Product Sales Sales Rep
Sioux Quad 10
Chantel Carlota 20
Chin Quad 30
Chantel Carlota 10
Chantel Quad 30
Sioux Carlota 10
Dino Quad 20

Spills down ê
Ex #24 Goal: Extract a distinct list of sales rep names from a column.
Sales Rep Product Sales Sales Rep
Sioux Quad 10 #NAME?
Chantel Carlota 20 #NAME?
Chin Quad 30 #NAME?
Chantel Carlota 10 #NAME?
Chantel Quad 30
Sioux Carlota 10
Dino Quad 20
Formula in cell F5: {=_xlfn.unique(B5:B11)}
Spills down ê
Ex #25 Goal: Extract a unique list of sales rep names from a column.
Sales Rep Product Sales Sales Rep
Sioux Quad 10
Chantel Carlota 20
Chin Quad 30
Chantel Carlota 10
Chantel Quad 30
Sioux Carlota 10
Dino Quad 20

Spills down ê
Ex #25 Goal: Extract a unique list of sales rep names from a column.
Sales Rep Product Sales Sales Rep
Sioux Quad 10 #NAME?
Chantel Carlota 20 #NAME?
Chin Quad 30
Chantel Carlota 10
Chantel Quad 30
Sioux Carlota 10
Dino Quad 20
Formula in cell F5: {=_xlfn.unique(B5:B11,,1)}
Spills down ê
Ex #26 Goal: Extract a distinct list of records (distinct rows) from a table.
Sales Rep Product Sales Sales Rep Product Sales
Sioux Quad 100
Chantel Carlota 200
Chantel Carlota 200
Chantel Carlota 200
Chantel Quad 100
Sioux Quad 100
Sioux Quad 100

Spills down ê
Ex #26 Goal: Extract a distinct list of records (distinct rows) from a table.
Sales Rep Product Sales Sales Rep Product Sales
Sioux Quad 100 #NAME? #NAME? #NAME?
Chantel Carlota 200 #NAME? #NAME? #NAME?
Chantel Carlota 200 #NAME? #NAME? #NAME?
Chantel Carlota 200
Chantel Quad 100
Sioux Quad 100
Sioux Quad 100
Formula in cell F5: {=_xlfn.unique(B5:D11)}
Spills down ê
Ex #27 Goal: Extract a distinct list of names from a row.

Sales Rep Sioux Chantel Chin Chantel Chantel Sioux Dino

Sales Rep

Spills to right è
Ex #27 Goal: Extract a distinct list of names from a row.

Sales Rep Sioux Chantel Chin Chantel Chantel Sioux Dino

Sales Rep #NAME? #NAME? #NAME? #NAME?

Formula in cell C6: {=_xlfn.unique(C4:I4,1)}

Spills to right è
Ex #28 Goal: Create two reports: 1) product sales report, and, 2) product count (frequency distribution) report.
Sales Rep Product Sales Product Total Sales Product
Sioux Quad 10
Chantel Carlota 20
Chin Quad 30
Chantel Carlota 10
Chantel Quad 30
Sioux Carlota 10 Both spill down ê
Dino Quad 20

Both spill down ê

uency distribution) report.
Count (Frequency)
Ex #28 Goal: Create two reports: 1) product sales report, and, 2) product count (frequency distribution) report.
Sales Rep Product Sales Product Total Sales Product
Sioux Quad 10 #NAME? #NAME? #NAME?
Chantel Carlota 20 #NAME? #NAME? #NAME?
Chin Quad 30
Chantel Carlota 10 Formula in cell F5: {=_xlfn.unique(st_12[Product])}
Chantel Quad 30 Formula in cell G5: {=SUMIFS(st_12[Sales],st_12[Product],_xlfn.an
Sioux Carlota 10 Both spill down ê
Dino Quad 20
Formula in cell I5: {=_xlfn.unique(st_12[Product])}
Formula in cell J5: {=COUNTIFS(st_12[Product],_xlfn.anchorarray(
Both spill down ê
uency distribution) report.
Count (Frequency)
#NAME?
#NAME?

ue(st_12[Product])}
t_12[Sales],st_12[Product],_xlfn.anchorarray(F5))}

ue(st_12[Product])}
(st_12[Product],_xlfn.anchorarray(I5))}
 array argument =
table, column or row that is the
source data

[sort_index] argument =
relative position of column/s
that you want to sort by
 array argument = [sort_order] argument =
table, column or row that is the 1 or omitted = A-Z (Small-Big)
source data -1 = Z-A (Big-Small)

SORT(array, [sort_index], [sort_order], [by_col])

[sort_index] argument = [by_col] argument =

relative position of column/s TRUE = sort by columns
that you want to sort by FALSE or omitted = sort by rows
Product Sales Product Sales
Quad 1 #NAME? #NAME?
Carlota 2 #NAME? #NAME?
Quad 5 #NAME? #NAME?
Sunset 8 #NAME? #NAME?
Sunset 3 #NAME? #NAME?

Product Quad Carlota Quad Sunset Sunset

Sales 1 2 5 8 3

Product #NAME? #NAME? #NAME? #NAME? #NAME?

Sales #NAME? #NAME? #NAME? #NAME? #NAME?
Ex #29 Goal: Sort the column distinct list A-Z.
Sales Rep Sales Rep
Sioux
Chantel
Chin
Chantel
Chantel
Sioux
Dino

Spills down ê
Ex #29 Goal: Sort the column distinct list A-Z.
Sales Rep Sales Rep
Sioux #NAME?
Chantel #NAME?
Chin #NAME?
Chantel #NAME?
Chantel
Sioux
Dino
Formula in cell D5: {=_xlfn._xlws.sort(_xlfn.unique(B5:B11))}
Spills down ê
Ex #30 Goal: Sort the column distinct list Z-A.
Sales Rep Sales Rep
Sioux
Chantel
Chin
Chantel
Chantel
Sioux
Dino

Spills down ê
Ex #30 Goal: Sort the column distinct list Z-A.
Sales Rep Sales Rep
Sioux #NAME?
Chantel #NAME?
Chin #NAME?
Chantel #NAME?
Chantel
Sioux
Dino
Formula in cell D5: {=_xlfn._xlws.sort(_xlfn.unique(B5:B11),,-1)}
Spills down ê
Ex #31 Goal: Sort the row distinct list A-Z.
Sales Rep Sioux Chantel Chin Chantel Chantel Sioux Dino

Sales Rep

Spills to right è
Ex #31 Goal: Sort the row distinct list A-Z.
Sales Rep Sioux Chantel Chin Chantel Chantel Sioux Dino

Sales Rep #NAME? #NAME? ### #NAME?

Formula in cell C6: {=_xlfn._xlws.sort(_xlfn.unique(C4:I4,1),,,1)}

Spills to right è
Ex #32 Goal: Select the racer & time columns and sort the records by time, smallest to biggest.
Date Track Racer Time Racer Time(s)
8/5/2022 SeaTac Isaac 38.8
8/5/2022 SeaTac Zaine 38.2
8/5/2022 SeaTac Ella 38.1
8/6/2022 Everett Zaine 37.5
8/6/2022 Everett Isaac 35.1
8/6/2022 Everett Ella 36.5

Spills down ê
Ex #32 Goal: Select the racer & time columns and sort the records by time, smallest to biggest.
Date Track Racer Time Racer Time(s)
8/5/2022 SeaTac Isaac 38.8 #NAME? #NAME?
8/5/2022 SeaTac Zaine 38.2 #NAME? #NAME?
8/5/2022 SeaTac Ella 38.1 #NAME? #NAME?
8/6/2022 Everett Zaine 37.5 #NAME? #NAME?
8/6/2022 Everett Isaac 35.1 #NAME? #NAME?
8/6/2022 Everett Ella 36.5 #NAME? #NAME?
Formula in cell G5: {=_xlfn._xlws.sort(D5:E10,2)}
Spills down ê
Ex #33 Goal: Sort the times for each trach smallest to biggest.
Date Track Racer Time Track Racer Time(s)
8/5/2022 SeaTac Isaac 38.8
8/5/2022 SeaTac Zaine 38.2
8/5/2022 SeaTac Ella 38.1
8/6/2022 Everett Zaine 37.5
8/6/2022 Everett Isaac 35.1
8/6/2022 Everett Ella 36.5

Spills down ê
Ex #33 Goal: Sort the times for each trach smallest to biggest.
Date Track Racer Time Track Racer Time(s)
8/5/2022 SeaTac Isaac 38.8 #NAME? ### #NAME?
8/5/2022 SeaTac Zaine 38.2 #NAME? ### #NAME?
8/5/2022 SeaTac Ella 38.1 #NAME? ### #NAME?
8/6/2022 Everett Zaine 37.5 #NAME? ### #NAME?
8/6/2022 Everett Isaac 35.1 #NAME? ### #NAME?
8/6/2022 Everett Ella 36.5 #NAME? ### #NAME?
Formula in cell G5: {=_xlfn._xlws.sort(C5:E10,{1,3},{-1,1})}
Spills down ê
Ex #34 Goal: Sort the times for each trach smallest to biggest.
Date Track Racer Time Track Racer Time(s)
8/5/2022 SeaTac Isaac 38.8
8/5/2022 SeaTac Zaine 38.2
8/5/2022 SeaTac Ella 38.1
8/6/2022 Everett Zaine 37.5
8/6/2022 Everett Isaac 35.1
8/6/2022 Everett Ella 36.5

Spills down ê
Ex #34 Goal: Sort the times for each track smallest to biggest.
Date Track Racer Time Track Racer Time(s)
8/5/2022 SeaTac Isaac 38.8 #NAME? ### #NAME?
8/5/2022 SeaTac Zaine 38.2 #NAME? ### #NAME?
8/5/2022 SeaTac Ella 38.1 #NAME? ### #NAME?
8/6/2022 Everett Zaine 37.5 #NAME? ### #NAME?
8/6/2022 Everett Isaac 35.1 #NAME? ### #NAME?
8/6/2022 Everett Ella 36.5 #NAME? ### #NAME?
Formula in cell G5: {=_xlfn.sortby(C5:E10,C5:C10,-1,E5:E10,1)}
Spills down ê
Formula in cell B11: {=CHAR(_xlfn.sequence(255))}

ASCII characters Start Sort A-Z Sort Z-A

#NAME? Number> 43 è #NAME? è #NAME?
#NAME? Text> {@J2*&% #NAME? #NAME?
#NAME? Zero Length Text String > #NAME? #NAME?
#NAME? Empty Cell > #NAME? #NAME?
#NAME? TRUE> TRUE #NAME? #NAME?
#NAME? Text> rad #NAME? #NAME?
#NAME? FALSE> FALSE #NAME? #NAME?
#NAME? Error> #DIV/0! #NAME? #NAME?
#NAME?
#NAME? Sort Button:
#NAME?
#NAME? 43 #DIV/0!
#NAME? TRUE
#NAME? {@J2*&% FALSE
#NAME? rad rad
#NAME? FALSE {@J2*&%
#NAME? TRUE
#NAME? #DIV/0! 43
#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?
Formula in cell H11: {=_xlfn._xlws.sort(F11:F18)}
Formula in cell J11: {=_xlfn._xlws.sort(F11:F18,,-1)}
 Start
Number> 43
Text> {@J2*&%
Zero Length Text String >
Empty Cell >
TRUE> TRUE
Text> rad
FALSE> FALSE
Error> #DIV/0!
Ex #35 Goal: Extract top 3, including records if there is a tie for third.
Name Score Top Top 3 hurdle Name Score
Shinea 25 3
Phil 10
Pham 10
Gigi 19
Ty 6
Chantel 22
Chin 15
Hue 23 Formula in cell G5:
Miki 10 Formula in cell I5:
Abdi 15
Tyrone 15
Sammi 22
Ex #35 Goal: Extract top 3, including records if there is a tie for third.
Name Score Top Top 3 hurdle Name Score
Shinea 25 3 22 #NAME? #NAME?
Phil 10 #NAME? #NAME?
Pham 10 #NAME? #NAME?
Gigi 19 #NAME? #NAME?
Ty 6
Chantel 22
Chin 15
Hue 23 Formula in cell G5: =LARGE(ScoreTable13[Score],E5)
Miki 10 Formula in cell I5: {=_xlfn._xlws.sort(_xlfn._xlws.filter(ScoreTable13[],ScoreTable13[Score]>=
Abdi 15
Tyrone 15
Sammi 22
13[],ScoreTable13[Score]>=G5),2,-1)}
rows argument = [columns] argument =
[start] argument =
how many rows in how many columns in
sequence start number
returned array returned array

SEQUENCE(rows, [columns], [start], [step])

[step] argument = the increment amount

to add to get to next number
 tart] argument =
uence start number

columns argument =
ncrement amount how many rows in
next number sequence
Ex #36 Goal: Demonstrate various types of number sequences.
rows 6 6
[columns]
[start] -15
[step]
Start at 1, Step 1 Start at -15, Step 1
 6 6

0 1
2 0
Start at 0, Step 2 All 1s
Size: 3 3

6 6

Repeat Cycle
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?

I12: {=ROUNDUP(_xlfn.sequence(I6)/I4,0)}
J12: {=MOD(_xlfn.sequence(J6,,0),J4)+1}
ence(I6)/I4,0)}
D(_xlfn.sequence(J6,,0),J4)+1}
Ex #36 Goal: Demonstrate various types of number sequences.
rows 6 6
[columns]
[start] -15
[step]
Start at 1, Step 1 Start at -15, Step 1
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?

C10: {=_xlfn.sequence(C4D10: {=_xlfn.sequence(D4,,D6

 6 6

0 1
2 0
Start at 0, Step 2 All 1s
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?

E10: {=_xlfn.sequence(E4,,E6,E7F10: {=_xlfn.sequence(F4,,F6,F7)}

Size: 3 3

6 6

Repeat Cycle
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?

I12: {=ROUNDUP(_xlfn.sequence(I6)/I4,0)}
J12: {=MOD(_xlfn.sequence(J6,,0),J4)+1}
ence(I6)/I4,0)}
D(_xlfn.sequence(J6,,0),J4)+1}
Ex #37 Goal: Demonstrate rectangular sequence.
rows 4
[columns] 4
[start]
[step]
Ex #37 Goal: Demonstrate rectangular sequence.
rows 4
[columns] 4
[start]
[step]
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?

C9: {=_xlfn.sequence(C4,C5)}
Ex #38 Goal: Demonstrate repeating number sequences.
Size: 3
rows 6
Step 1 Step 2 Step 3
All in one formula:
Repeat
Ex #38 Goal: Demonstrate repeating number sequences.
Size: 3
rows 6
Step 1 Step 2 Step 3
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?

C8: {=_xlfn.sequence(CD8: {=_xlfn.ancE8: {=ROUNDUP(_xlfn.anc

All in one formula:
Repeat
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?

F8: {=ROUNDUP(_xlfn.sequence(C5)/C4,0)}
Ex #39 Goal: Demonstrate cyclical number sequences.
Size: 3
rows 6
start 0
Step 1 Step 2 Step 3

** Note: MOD gives you the remainder after performing division. For example MOD(4,3) = 1 because 4/3 = 1
 All in one formula:
Cyclical

n. For example MOD(4,3) = 1 because 4/3 = 1 R 1.

Ex #39 Goal: Demonstrate cyclical number sequences.
Size: 3
rows 6
start 0
Step 1 Step 2 Step 3
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?

C9: {=_xlfn.sequence(C5,,CD9: {=MOD(_xlfn.anchE9: {=_xlfn.an

** Note: MOD gives you the remainder after performing division. For example MOD(4,3) = 1 because 4/3 = 1
 All in one formula:
Cyclical
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?

F9: {=MOD(_xlfn.sequence(C5,,0),C4)+1}

n. For example MOD(4,3) = 1 because 4/3 = 1 R 1.

Data Set: Qualitative Frequency Distribution
Product Sales Product Frequency
Quad $173.16 #NAME? #NAME?
Aspen $22.95 #NAME? #NAME?
Quad $91.72 #NAME? #NAME?
Aspen $33.32
Aspen $50.00
Carlota $18.91 Count = 3 Carlotas
Carlota $40.10
Carlota $27.89
Quad $143.69 All possible
Quad $69.69 categories
Quad $17.09 (distinct list)
Aspen $83.97
Aspen $78.40
Quad $198.27
 Increment 50
Min $17.09
Max $198.27
rows 4 Start 0
start 50 Upper Limit Last Category 200
step 50 rows 4

Quantitative Frequency Distribution

Sales Count Category Frequency
#NAME? #NAME?
Count = 4 sales
#NAME? #NAME? > $50 AND <= $100
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
Carlotas

All possible
categories
(bins with upper
and lower limit)
#NAME?
#NAME?
#NAME?
#NAME?
Ex #40 Goal: Create qualitative frequency distribution for product variable.
Data Set: Qualitative Frequency Distribution
Product Sales Product Frequency
Quad $173.16
Aspen $22.95
Quad $91.72
Aspen $33.32
Aspen $50.00
Carlota $18.91
< Hidden rows Quad $198.27
Ex #40 Goal: Create qualitative frequency distribution for product variable.
Data Set: Qualitative Frequency Distribution
Product Sales Product Frequency
Quad $173.16 #NAME? #NAME?
Aspen $22.95 #NAME? #NAME?
Quad $91.72 #NAME? #NAME?
Aspen $33.32
Aspen $50.00 F7: {=_xlfn._xlws.sort(_xlfn.unique(C7:C20))}
Carlota $18.91 G7: {=COUNTIFS(C7:C20,_xlfn.anchorarray(F7))}
< Hidden rows Quad $198.27
 bins_array argument =
data_array argument =
upper limits for counting
number source data
categories

FREQUENCY(data_array, bins_array)

#NAME?
Ex #41 Goal: Create quantitative frequency distribution for sales variable.
Data Set: Quantitative Frequency Distribution

Product Sales $ Sales Upper Limit (included) Frequency

Quad $173.16 $50
Aspen $22.95 $100
Quad $91.72 $150
Aspen $33.32 $200
Aspen $50.00
Carlota $18.91
Carlota $40.10
Carlota $27.89
Quad $143.69
Quad $69.69
Quad $17.09
Aspen $83.97
Aspen $78.40
Quad $198.27
< extra category that FREQUENCY
creates to catch any values
bigger than last upper limit
Ex #41 Goal: Create quantitative frequency distribution for sales variable.
Data Set: Quantitative Frequency Distribution

Product Sales $ Sales Upper Limit (included) Frequency

Quad $173.16 $50 7
Aspen $22.95 $100 4
Quad $91.72 $150 1
Aspen $33.32 $200 2
Aspen $50.00 0
Carlota $18.91
Carlota $40.10
Quad $198.27 < Hidden data rows

Formula in cell F7: {=FREQUENCY(C7:C20,E7:E10)}

 < extra category that FREQUENCY
creates to catch any values
bigger than last upper limit

(C7:C20,E7:E10)}
Ex #41 Goal: Create quantitative frequency distribution for sales variable.
Data Set: Quantitative Frequency Distribution

Product Sales $ Sales Upper Limit (included) Frequency

Quad $173.16 $50 7
Aspen $22.95 $100 4
Quad $91.72 $150 1
Aspen $33.32 $200 2
Aspen $50.00 0
Carlota $18.91
Carlota $40.10
Carlota $27.89
Quad $143.69
Quad $69.69
Quad $17.09
Aspen $83.97
Aspen $78.40
Quad $198.27

Formula in cell F7: {=FREQUENCY(C7:C20,E7:E10)}

 Categories created by FREQUENCY:
Sale <= $50
$50 < Sales <= $100
$100 < Sales <= $150
$150 < Sales <= $200
$200 < Sales

(C7:C20,E7:E10)}
Ex #42 Goal: Remove last counting category that FREQUENCY array function creates.
Data Set: Quantitative Frequency Distribution

Product Sales $ Sales Upper Limit (included) Frequency

Quad $173.16 $50
Aspen $22.95 $100
Quad $91.72 $150
Aspen $33.32 $200
Aspen $50.00
Carlota $18.91 Formula in cell F7:
Carlota $40.10
Carlota $27.89
Quad $198.27 < Hidden data rows
ction creates.
Ex #42 Goal: Remove last counting category that FREQUENCY array function creates.
Data Set: Quantitative Frequency Distribution

Product Sales $ Sales Upper Limit (included) Frequency

Quad $173.16 $50 #NAME?
Aspen $22.95 $100 #NAME?
Quad $91.72 $150 #NAME?
Aspen $33.32 $200 #NAME?
Aspen $50.00
Carlota $18.91 Formula in cell F7:
Carlota $40.10 {=INDEX(FREQUENCY(C7:C20,E7:E10),_xlfn.sequence(ROWS(E7:E10)))}
Carlota $27.89
Quad $198.27 < Hidden data rows
ction creates.

E10),_xlfn.sequence(ROWS(E7:E10)))}
Ex #43 Goal: Full process to create quantitative frequency distribution for sales variable.
Data Set: Min
Max
Product Sales Lower limit of 1st category
Quad $173.16 Upper limit last category
Aspen $22.95 Width of each bin 50
Quad $91.72 rows
Aspen $33.32 start
Aspen $50.00 step
Carlota $18.91
Carlota $40.10 Quantitative Frequency Distribution
Carlota $27.89
Quad $143.69 $ Sales Upper Limit (included) Frequency
Quad $69.69
Quad $17.09
Aspen $83.97
Aspen $78.40
Quad $198.27
r sales variable.
Ex #43 Goal: Full process to create quantitative frequency distribution for sales variable.
Data Set: Min $17.09
Max $198.27
Product Sales Lower limit of 1st category $0.00
Quad $173.16 Upper limit last category $200.00
Aspen $22.95 Width of each bin 50
Quad $91.72 rows 4
Aspen $33.32 start $50.00
Aspen $50.00 step $50.00
Carlota $18.91
Carlota $40.10 Quantitative Frequency Distribution
Carlota $27.89
Quad $143.69 $ Sales Upper Limit (included) Frequency
Quad $69.69 #NAME? #NAME?
Quad $17.09 #NAME? #NAME?
Aspen $83.97 #NAME? #NAME?
Aspen $78.40 #NAME? #NAME?
Quad $198.27 #NAME? #NAME?

Formula in cell E16: {=_xlfn.sequence(F9,,F10,F11)}

Formula in cell F16: {=FREQUENCY(C7:C20,_xlfn.anchorarray(E16))}
Formula in cell E20: =">$"&E19
r sales variable.
◄ cell F4: =MIN(C7:C20)
◄ cell F5: =MAX(C7:C20)
◄ cell F6: =FLOOR.XCL(F4,F8)
◄ cell F7: =CEILING.XCL(F5,F8)

◄ cell F9: =F7/F8

◄ cell F10: =F8
◄ cell F11: =F8

0,_xlfn.anchorarray(E16))}
Ex #44 Goal: Full process to create quantitative frequency distribution for sales variable.
Data Set: Min $17.09
Max $198.27
Product Sales Lower limit of 1st category $0.00
Quad $173.16 Upper limit last category $200.00
Aspen $22.95 Width of each bin 50
Quad $91.72 rows 4
Aspen $33.32 start $50.00
Aspen $50.00 step $50.00
Carlota $18.91
Carlota $40.10 Quantitative Frequency Distribution
Carlota $27.89
Quad $143.69 $ Sales Upper Limit (included) Frequency
Quad $69.69 #NAME? #NAME?
Quad $17.09 #NAME? #NAME?
Aspen $83.97 #NAME? #NAME?
Aspen $78.40 #NAME? #NAME?
Quad $198.27 #NAME? #NAME?

Formula in cell E16: {=_xlfn.sequence(F9,,F10,F11)}

Formula in cell F16: {=FREQUENCY(C7:C20,_xlfn.anchorarray(E16))}
Formula in cell E20: =">$"&E19
r sales variable.
◄ cell F4: =MIN(C7:C20)
◄ cell F5: =MAX(C7:C20)
◄ cell F6: =FLOOR.XCL(F4,F8)
◄ cell F7: =CEILING.XCL(F5,F8)

◄ cell F9: =F7/F8

◄ cell F10: =F8
◄ cell F11: =F8

Categories created by FREQUENCY:

#NAME?
#NAME?
#NAME?
#NAME?
#NAME?

0,_xlfn.anchorarray(E16))}
◄ Formula in cell H16: =" Sale <= "&DOLLAR(E16,0)
◄ Formula in cell H17: =DOLLAR(E16,0)&" < Sales <= "&DOLLAR(E17,0)
◄ Formula in cell H18: =DOLLAR(E17,0)&" < Sales <= "&DOLLAR(E18,0)
◄ Formula in cell H19: =DOLLAR(E18,0)&" < Sales <= "&DOLLAR(E19,0)
◄ Formula in cell H20: =DOLLAR(E19,0)&" < Sales"
Ex #44 Goal: Full process to create quantitative frequency distribution for sales variable.
Data Set: Min $17.09
Max $198.27
Product Sales Lower limit of 1st category $0.00
Quad $173.16 Upper limit last category $200.00
Aspen $22.95 Width of each bin 50
Quad $91.72 rows 4
Aspen $33.32 start $50.00
Aspen $50.00 step $50.00
Carlota $18.91
Carlota $40.10 Quantitative Frequency Distribution
Carlota $27.89
Quad $143.69 $ Sales Upper Limit (included) Frequency
Quad $69.69 #NAME? #NAME?
Quad $17.09 #NAME? #NAME?
Aspen $83.97 #NAME? #NAME?
Aspen $78.40 #NAME? #NAME?
Quad $198.27 #NAME? #NAME?

Formula in cell E16: {=_xlfn.sequence(F9,,F10,F11)}

Formula in cell F16: {=FREQUENCY(C7:C20,_xlfn.anchorarray(E16))}
Formula in cell E20: =">$"&E19
r sales variable.
◄ cell F4: =MIN(C7:C20)
◄ cell F5: =MAX(C7:C20)
◄ cell F6: =FLOOR.XCL(F4,F8)
◄ cell F7: =CEILING.XCL(F5,F8)

◄ cell F9: =F7/F8

◄ cell F10: =F8
◄ cell F11: =F8

Categories created by FREQUENCY:

0,_xlfn.anchorarray(E16))}
Ex #45 Goal: Create quantitative frequency distribution for sales variable with COUNTIFS function.
Data Set: Quantitative Frequency Distribution

Lower Limit Upper Limit

Product Sales (included) (not included) Frequency Categories for COUNTIFS:
Quad $173.16 #NAME?
Aspen $22.95 #NAME?
Quad $91.72 #NAME?
Aspen $33.32 #NAME?
Carlota $18.91
Carlota $40.10
Carlota $27.89 Inputs for SEQUENCE:
Quad $143.69 rows start
Aspen $50.00 4 0
Quad $69.69 4 50
Quad $17.09
Aspen $83.97
Aspen $78.40
Quad $198.27
ories for COUNTIFS:

for SEQUENCE:
step
50
50
Ex #45 Goal: Create quantitative frequency distribution for sales variable with COUNTIFS function.
Data Set: Quantitative Frequency Distribution

Lower Limit Upper Limit

Product Sales (included) (not included) Frequency Categories for COUNTIFS:
Quad $173.16 #NAME? #NAME? #NAME? #NAME?
Aspen $22.95 #NAME? #NAME? #NAME? #NAME?
Quad $91.72 #NAME? #NAME? #NAME? #NAME?
Aspen $33.32 #NAME? #NAME? #NAME? #NAME?
Carlota $18.91
Carlota $40.10
Carlota $27.89 Inputs for SEQUENCE:
Quad $143.69 rows start
Aspen $50.00 Formula in cell E7: {=_xlfn.sequence(I15,,J15,K15)} 4 0
Quad $69.69 Formula in cell F7: {=_xlfn.sequence(I16,,J16,K16)} 4 50
Quad $17.09
Aspen $83.97 Formula in cell G7: {=COUNTIFS(C7:C20,">="&_xlfn.anchorarray(E7),C7:C20,"<"&_xlfn.anchor
Aspen $78.40
Quad $198.27
ories for COUNTIFS:
Formula in cell I7: {=DOLLAR(_xlfn.sequence(I15,,J15,K15),0)&" >= Sales < "&DOLLAR(_xlfn.sequence(I16,,J16,K16),

for SEQUENCE:
step
50
50

ay(E7),C7:C20,"<"&_xlfn.anchorarray(F7))}
xlfn.sequence(I16,,J16,K16),0)}
Ex #39 Goal: a.
Pattern of Test Scores

Category Option 1
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?

Raw Data: è Counting based on an upper limit:

Test Scores Frequency Distribution #1:
43 Test Grade
57 Test Score Upper Limits Frequency
68 #NAME? #NAME?
50 #NAME? #NAME?
69 #NAME? #NAME?
Frequency

75 #NAME? #NAME?
66 #NAME? #NAME?
90 #NAME? #NAME?
41 #NAME? #NAME?
37 #NAME? #NAME? E? E? E? E?
AM AM AM AM A
#N #N #N #N #N
Test
 Freq
E? E? E? E?
AM AM AM AM A
80 #NAME? #NAME? #N #N #N #N #N
53 #NAME? #NAME?
Test
61 #NAME? #NAME?
78
90 < Hidden data rows
56

Raw Data: è Counting based on an upper limit:

$ Sales at Target Frequency Distribution #2:
$15.21 Pattern for
$8.80 $ Sales Upper Limits Frequency
$22.26 #NAME? #NAME?
$35.30 #NAME? #NAME?
$15.53 #NAME? #NAME?

Frequency
$19.12 #NAME? #NAME?
$25.53 #NAME? #NAME?
$27.58 #NAME? #NAME?
$182.88 #NAME? #NAME?
$5.47 #NAME? #NAME? E? E? E? E?
AM AM AM AM A
$14.74 #NAME? #NAME? #N #N #N #N #N
$17.56 #NAME? #NAME?
$
$9.94 #NAME? #NAME?
$44.21 < Hidden data rows
$4.62
 Formula:
#NAME?

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

Test Grades From Excel Class

Frequency

E? E? E? E? E? E? E? E? E? E? E?
AM AM AM AM AM AM AM AM AM AM AM
#N #N #N #N #N #N #N #N #N #N #N
Test Score Upper Limts
Freq

E? E? E? E? E? E? E? E? E? E? E?
AM AM AM AM AM AM AM AM AM AM AM
#N #N #N #N #N #N #N #N #N #N #N
Test Score Upper Limts

Pattern for $ Sales at Target

Frequency

E? E? E? E? E? E? E? E? E? E? E?
AM AM AM AM AM AM AM AM AM AM AM
#N #N #N #N #N #N #N #N #N #N #N
$ SalesUpper Limts
Goal: a.
Pattern for $ Sales at Target

Category Option 1
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
 Formula:
#NAME?

SEQUENCE
rows 10
[columns]
[start] 25
[step] 50
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'.
For extra credit, see if you can calculate the standard deviation in cell E15.
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:

Standard Deviation of Ex. 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'.
For extra credit, see if you can calculate the standard deviation in cell E15.
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

Standard Deviation of Ex. Portfolio Returns: 0.09064704904
Standard Deviation of Ex. Portfolio Returns: 0.09064704904
Standard Deviation of Ex. Portfolio Returns: #NAME?

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

Formula in cell E15: {=SQRT(SUM((MMULT(D9:F12*D7:F7,{1;1;1})-E14)^2*C9:C12))}
Formula in cell E16: {=SQRT(SUM((MMULT(D9:F12*D7:F7,TRANSPOSE(COLUMN(D7:F7)^0))-E14)^2*C9:C12))}
Formula in cell E17: {=SQRT(SUM((MMULT(D9:F12*D7:F7,_xlfn.sequence(COLUMNS(D7:F7),1,1,0))-E14)^2*C9:

Check if probability is = 1
1
(D7:F7)^0))-E14)^2*C9:C12))}
MNS(D7:F7),1,1,0))-E14)^2*C9:C12))}
Goal: Create a formula in cell H5: to spill a column of row totals.

Week 1 Units Week 2 Units Week 3 Units Week 4 Units

Sales Rep Sold Sold Sold Sold Totals
Chun 44 11 218 13
Xiaver 249 138 212 105
Shinia 150 239 188 33
Billy 46 119 67 90
Goal: Create a formula in cell H5: to spill a column of row totals.

Week 1 Units Week 2 Units Week 3 Units Week 4 Units

Sales Rep Sold Sold Sold Sold Totals
Chun 44 11 218 13 #NAME?
Xiaver 249 138 212 105 #NAME?
Shinia 150 239 188 33 #NAME?
Billy 46 119 67 90 #NAME?

Formula in cell H5:

{=MMULT(D5:G8,_xlfn.sequence(COLUMNS(D5
n.sequence(COLUMNS(D5:G8),,1,0))}
Goal: Create a formula in cell J5: extract the Sales Rep column and the Week 2 Units Sold Column.

Week 1 Week 2 Week 3 Week 4 Week 2

Sales Rep Units Sold Units Sold Units Sold Units Sold Totals Sales Rep Units Sold
Chun 44 11 218 13 #NAME?
Xiaver 249 138 212 105 #NAME?
Shinia 150 239 188 33 #NAME?
Billy 46 119 67 90 #NAME?
Goal: Create a formula in cell J5: extract the Sales Rep column and the Week 2 Units Sold Column.

Week 1 Units Week 2 Units Week 3 Units Week 4 Units

Sales Rep Sold Sold Sold Sold Totals
Chun 44 11 218 13 #NAME?
Xiaver 249 138 212 105 #NAME?
Shinia 150 239 188 33 #NAME?
Billy 46 119 67 90 #NAME?
 Week 2
Sales Rep Units Sold
#NAME? #NAME? Formula in cell J5:
#NAME? #NAME? {=_xlfn._xlws.filter(C5:G8,ISNUMBER(_xlfn.xmatch(C4:G4,J4:K4)))}
#NAME? #NAME?
#NAME? #NAME?
G4,J4:K4)))}
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(C
Shinia 1/1/2022 332 #NAME? #NAME?
Billy 1/1/2022 248 #NAME? #NAME? Formula in cell H6:
Chun 1/2/2022 343 {=SUMIFS(CallsHW55[Time on Ca
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(CallsHW55[Sales Rep]))}

S(CallsHW55[Time on Calls (min)],CallsHW55[Sales Rep],_xlfn.anchorarray(G6))}

Goal: Using the FILTER function, filter the data set to show records that contains a time value.
Format the filtered data set.

Time on
Time on Calls Calls
Sales Rep Date (min) Sales Rep Date (min)
Chun 1/1/2022 218
Xiaver 1/1/2022 418
Shinia 1/1/2022 332
Billy 1/1/2022
Chun 1/2/2022 343
Xiaver 1/2/2022 183
Shinia 1/2/2022 223
Billy 1/2/2022
Chun 1/3/2022 389
Xiaver 1/3/2022 444
Shinia 1/3/2022 473
Billy 1/3/2022
Chun 1/4/2022 192
Xiaver 1/4/2022 217
Shinia 1/4/2022 122
Goal: Using the FILTER function, filter the data set to show records that contains a time value.
Format the filtered data set.

Time on
Time on Calls Calls
Sales Rep Date (min) Sales Rep Date (min)
Chun 1/1/2022 218 #NAME? #NAME? #NAME? Formula in cell G6:
Xiaver 1/1/2022 418 #NAME? #NAME? #NAME? {=_xlfn._xlws.filter(Calls
Shinia 1/1/2022 332 #NAME? #NAME? #NAME?
Billy 1/1/2022 #NAME? #NAME? #NAME?
Chun 1/2/2022 343 #NAME? #NAME? #NAME?
Xiaver 1/2/2022 183 #NAME? #NAME? #NAME?
Shinia 1/2/2022 223 #NAME? #NAME? #NAME?
Billy 1/2/2022 #NAME? #NAME? #NAME?
Chun 1/3/2022 389 #NAME? #NAME? #NAME?
Xiaver 1/3/2022 444 #NAME? #NAME? #NAME?
Shinia 1/3/2022 473 #NAME? #NAME? #NAME?
Billy 1/3/2022 #NAME? #NAME? #NAME?
Chun 1/4/2022 192
Xiaver 1/4/2022 217
Shinia 1/4/2022 122
Formula in cell G6:
{=_xlfn._xlws.filter(CallsHW5Answer[],CallsHW5Answer[Time on Calls (min)])}
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(ScoresHW7Answer[],E6)
Miki 10 Formula in cell I6: {=_xlfn._xlws.sort(_xlfn._xlws.filter(ScoresHW7Answer[],ScoresHW7Answer[
Abdi 15
Tyrone 19
Sammi 22
Answer[],ScoresHW7Answer[Score]>=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 #1

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
Ex #40 Goal: a.
Test Grades From Excel Class

Test Grades Category Option 1 Test Score Upper Limts

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

56

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

? ? ? ? ? ? ?
Test Score Upper Limts
 Frequency SEQUENCE
#NAME? rows 10
#NAME? [columns]
#NAME? [start] 10
#NAME? [step] 10
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?

Test Grades From Excel Class

E #NAME #NAME #NAME #NAME #NAME #NAME #NAME #NAME #NAME

? ? ? ? ? ? ? ? ?
Test Score Upper Limts
Formula:
#NAME?
Ex #41 Goal: a.
Pattern for $ Sales at Target

$ Sales at Target Category Option 1 $ SalesUpper Limts

$15.21 #NAME? #NAME?
$8.80 #NAME? #NAME?
$22.26 #NAME? #NAME?
$35.30 #NAME? #NAME?
$15.53 #NAME? #NAME?
$19.12 #NAME? #NAME?
$25.53 #NAME? #NAME?
$27.58 #NAME? #NAME?
$182.88 #NAME? #NAME?
$5.47 #NAME? #NAME?
$14.74 #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 #NA
$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
 Frequency SEQUENCE
#NAME? rows 10
#NAME? [columns]
#NAME? [start] 25
#NAME? [step] 50
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?

ern for $ Sales at Target

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

? ? ? ? ? ? ? ?
$ SalesUpper Limts
Formula:
#NAME?
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,
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?

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
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
tion 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