ArrayConstants
S. California Pacific NW SouthWest Central SouthEast NorthEast
Entered using the
TRANSPOSE function.
S. California
Pacific NW
A 6-element vertical
SouthWest array.
Central
SouthEast
NorthEast
This workbook has a vertical array constant, named SalesRegions. The constant was created
using the Name A Range command. SalesRegions is defined as:
={"S. California";"Pacific NW";"SouthWest";"Central";"SouthEast";"NorthEast"}
Page 1
[Link]
Test value: Buddy
Names
Homer David Bud Name not found
Bill Carl Jeremy
Frank Herman Annette
Louis Jack Warren This worksheet demonstrates
Lori Homer Phil how to use an array formula
to determine if a value (or
Jill Bart Toby text) is contained in a
Joice Marge Shirley particular range.
Ken Gail Anthony
Jeff Sally Tanya
Stephanie Al Gomer
Page 2
CountLetters
Wordlist 74 = Number of letters in WordList
January
February
March
April
May
June
This array formula returns
July the number of letters in
August the range.
September
October
November
December
Page 3
MaxMin_Change
Test 1 Test 2
Student 1 45 56 -5 Largest Decrease
Student 2 78 73 11 Largest Increase
Student 3 91 93
Student 4 62 69
Student 5 74 71
Student 6 87 87
Student 7 81 89
Student 8 83 80
Student 9 53 53
This worksheet demonstrates
how an array formula can
eliminate the need for
intermediary formulas.
Page 4
Sum of Digits
7845 <--- Number
24 < --- Sum of the digits (array formula)
This array formula processes each digit
in a cell. It returns the sum of the
digits of an integer contained in the
cell named Number.
Page 5
Sum Every Nth
N: 3 = nth value
1,683 = Result returned by a single array formula
1,683 = Result returned by using formulas in column B:C
Data Array formulas Formulas
This workbook demonstrate how to use an
1 1 array formula to return the sum of every nth
2 2 value in a vertical range. The range is named
Data.
3 0 3
4 1 The array formula does not require the
5 2 intermediary formulas in columns C and D.
6 0 6 Enter "n" in cell C2
7 1
8 2
9 0 9
10 1
11 2
12 0 12
13 1
14 2
15 0 15
16 1
17 2
18 0 18
19 1
20 2
21 0 21
22 1
23 2
24 0 24
25 1
26 2
27 0 27
28 1
29 2
30 0 30
31 1
32 2
33 0 33
34 1
35 2
36 0 36
Page 6
Sum Every Nth
37 1
38 2
39 0 39
40 1
41 2
42 0 42
43 1
44 2
45 0 45
46 1
47 2
48 0 48
49 1
50 2
51 0 51
52 1
53 2
54 0 54
55 1
56 2
57 0 57
58 1
59 2
60 0 60
61 1
62 2
63 0 63
64 1
65 2
66 0 66
67 1
68 2
69 0 69
70 1
71 2
72 0 72
73 1
74 2
75 0 75
76 1
77 2
78 0 78
79 1
Page 7
Sum Every Nth
80 2
81 0 81
82 1
83 2
84 0 84
85 1
86 2
87 0 87
88 1
89 2
90 0 90
91 1
92 2
93 0 93
94 1
95 2
96 0 96
97 1
98 2
99 0 99
100 1
Page 8
[Link]
Ranks With
Excel's Rank Array
Salesperson Sales Function Formula
Adams 123,000 6 6
Bigelow 98,000 9 10
Fredericks 98,000 9 10 Assigned middle rank
Georgio 98,000 9 10
Jensen 25,000 12 12
Juarez 101,000 8 8
Klein 305,000 1 1
Lynch 145,000 3 3.5
Mayne 145,000 3 3.5 Assigned average rank
Roberton 121,000 7 7
Slokum 124,000 5 5
Wu 150,000 2 2
This worksheet demonstrates how to use
array formulas to produce rank orders that
handle ties better than Excel's RANK()
function. If items are tied, each is assigned
the middle (or average) ranking.
Page 9
[Link]
Dates Categories Amounts
1-Jan Food 23.50 Transp Food Lodging
1-Jan Transp 15.00 1-Jan 160.50 49.57 65.95
1-Jan Food 9.12 2-Jan 20.00 27.80 89.00
1-Jan Food 16.95 3-Jan 0.00 101.96 75.30
1-Jan Transp 145.50 4-Jan 11.50 25.00 112.00
1-Jan Lodging 65.95
2-Jan Transp 20.00
2-Jan Food 7.80
2-Jan Food 20.00
2-Jan Lodging 89.00 {=SUM(IF($E3&F$2=Dates&Categories,Amounts))}
3-Jan Food 9.00
3-Jan Food 3.50
This worksheet demonstrates
3-Jan Food 11.02 how to create a dynamic
3-Jan Food 78.44 crosstab table using a array
3-Jan Lodging 75.30 formulas. A pivot table is much
more efficient.
4-Jan Transp 11.50
4-Jan Food 15.50
4-Jan Food 9.50
4-Jan Lodging 112.00
Page 10
June, 2025
Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30
