single-cell array formulas
TRANSCRIPT
-
8/8/2019 single-cell array formulas
1/22
Single-cell array formula examples
Note: All of the names in this workbook are sheet-level names.
-
8/8/2019 single-cell array formulas
2/22
-
8/8/2019 single-cell array formulas
3/22
Counting error values in a range
0 12 #DIV/0! #N/A
1 13 13 #NAME?
2 14 7 2
3 15 5 3
4 16 4 45 17 3.4 5
6 18 3 6
7 19 2.71 7
8 20 2.5 8
9 21 Err:502 9
10 22 2.2 10
11 23 2.09 11
0
-
8/8/2019 single-cell array formulas
4/22
Conditional summing
-3 12
-
8/8/2019 single-cell array formulas
5/22
Summing the n largest values
5 9 13
15 19 23
15 19 23
17 21 25
19 23 2720 24 28
22 26 30
24 28 32
35 39 43
37 41 45
43 47 51
100 101 102
Number to sum? 3Sum: 24
-
8/8/2019 single-cell array formulas
6/22
-
8/8/2019 single-cell array formulas
7/22
Is a value contained in a range?
Enter a Name --> Curt Found
Al Daniel Harold Lyle Richard
Allen Dave Ian Maggie Rick
Andrew David Jack Margaret Robert
Anthony Dennis James Marilyn Rod
Arthur Don Jan Mark Roger
Barbara Donald Jeff Marvin Ronald
Bernard Doug Jeffrey Mary Russ
Beth Douglas Jerry Matt Sandra
Bill Ed Jim Mel Scott
Bob Edward Joe Merle Simon
Brian Eric John Michael Stacy
Bruce Fran Joseph Michelle Stephen
Cark Frank Kathy Mike StevenCarl Fred Kathy Norman Stuart
Charles Gary Keith Patrick Susan
Chris George Kenneth Paul Terry
Chuck Glenn Kevin Peter Thomas
Clark Gordon Larry Phillip Timothy
Curt Greg Leonard Ray Vincent
Dan Gregory Louise Rebecca Wendy
Non-array formula: Curt Found
-
8/8/2019 single-cell array formulas
8/22
Counting the number of differences between two ranges
MyData YourData
1 34 1 34 FALSE FALSE 0
3 35 3 35 FALSE FALSE 0
5 36 5 36 FALSE FALSE 0
7 37 7 37 FALSE FALSE 09 38 9 38 FALSE FALSE 0
11 39 11 39 FALSE FALSE 0
13 40 13 40 FALSE FALSE 0
15 41 14 41 TRUE FALSE 1
17 42 17 42 FALSE FALSE 0
19 43 19 43 FALSE FALSE 0
21 44 21 44 FALSE FALSE 0
23 45 23 43 FALSE TRUE 0
25 46 25 46 FALSE FALSE 0
27 47 27 47 FALSE FALSE 029 48 29 48 FALSE FALSE 0
31 49 31 49 FALSE FALSE 0
33 50 33 50 FALSE FALSE 0
Differences found 2
Alternate version: 2
-
8/8/2019 single-cell array formulas
9/22
0
0
0
00
0
0
0
0
0
0
1
0
00
0
0
-
8/8/2019 single-cell array formulas
10/22
Location of the maximum value in a range
1 Maximum value: 8
3 Row of maximum value: 10
5 Address of maximum valu $A$10
6
912
15
22
32
44
32
55
78
49
41
-
8/8/2019 single-cell array formulas
11/22
The row of the nth occurrence of a value
1 Value to find: 1
3 Occurrence of Value: 3
3 It's in row: Err:502
66
6
8
8
3
8
8
8
8
11
-
8/8/2019 single-cell array formulas
12/22
Longest text in a range
January
February Longest text: 1
March
AprilMay
June
July
August
September
October
November
December
-
8/8/2019 single-cell array formulas
13/22
Valid items in a range
Master MyList
AZ-101 AZ-109 All items valid? FALSE
AZ-102 AZ-105 Invalid items: 2
AZ-103 AZ-109 First invalid ite AZ-121
AZ-104 AZ-107AZ-105 AZ-121
AZ-106 AZ-122
AZ-107
AZ-108
AZ-109
AZ-110
AZ-111
AZ-112
AZ-113
AZ-114AZ-115
AZ-116
-
8/8/2019 single-cell array formulas
14/22
Sum of the digits of a value
Number Sum of Digits
132 6
9 9
111111 6
980991 36-980991 36
409 13
0
12 3
123 6
-
8/8/2019 single-cell array formulas
15/22
Summing rounded values
Description Quantity nit PriceDiscount Total
Widgets 6 $11.69 5.23% $66.47
Sprockets 8 $9.74 5.23% $73.84
Snapholytes 3 $9.85 5.23% $28.00
GRAND TOTAL $168.32
-
8/8/2019 single-cell array formulas
16/22
-
8/8/2019 single-cell array formulas
17/22
Removing non-numeric characters from a cell
AZ434 AZ434
XT656 XT656
5T 5T
T5 T55T5 5T5
-
8/8/2019 single-cell array formulas
18/22
Determining the closest value in a range
-12 Target Value 45
-4 Closest Matc 8
4
1220
32
40
48
56
72
80
88
96
97105
137
145
165
173
-
8/8/2019 single-cell array formulas
19/22
Returning the last value in a column or row
Data 101
-
8/8/2019 single-cell array formulas
20/22
Ranking data with an array formula
Sales
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
Salesperson
Excel'sRank
Function
Ranks WithArray
Formula
-
8/8/2019 single-cell array formulas
21/22
Dynamic crosstabulation
Date Categor Amount
1/4/2007 Food 23.50 Transp Food Lodging
1/4/2007 Transp 15.00 4-Jan 160.50 49.57 65.95
1/4/2007 Food 9.12 5-Jan 20.00 27.80 89.001/4/2007 Food 16.95 6-Jan 0.00 101.96 75.30
1/4/2007 Transp 145.50 7-Jan 11.50 25.00 112.00
1/4/2007 Lodging 65.95
1/5/2007 Transp 20.00
1/5/2007 Food 7.80
1/5/2007 Food 20.00
1/5/2007 Lodging 89.00
1/6/2007 Food 9.00
1/6/2007 Food 3.50
1/6/2007 Food 11.021/6/2007 Food 78.44
1/6/2007 Lodging 75.30
1/7/2007 Transp 11.50
1/7/2007 Food 15.50
1/7/2007 Food 9.50
1/7/2007 Lodging 112.00
-
8/8/2019 single-cell array formulas
22/22
Summing every nth value
Data
1 5 =nth
2 31 = Result
3
4 21 Alternate method5
6
7
8
9
10
11
12
13
1415
16
17
18
19