Week 2: Learning Objectives

Use the following functionalities in Excel for Advanced Formula Techniques:

Apply structured references in formulas
Explain the use of array formulas
Build formulas with array functions
Use array formulas in calculations
Excel Skills for Business: Advanced
Week 2: Advanced Formula Techniques
Final Assessment
ls for Business: Advanced
dvanced Formula Techniques
Final Assessment
Section 1 - Tables
Celsius = (Fahrenheit - 32)
Table Name: City_Subset

Location High_F Low_F High (C)

Atlanta, Georgia 72 53 22.2
Austin, Texas 80 59 26.7
Baltimore, Maryland 65 45 18.3
Birmingham, Alabama 74 53 23.3
Boston, Massachusetts 59 44 15.0
Buffalo, New York 56 40 13.3
Charlotte, North Carolina 71 49 21.7
Chicago, Illinois 59 41 15.0
Cincinnati, Ohio 65 43 18.3
Cleveland, Ohio 60 43 15.6
Columbus, Ohio 63 44 17.2
Dallas, Texas 77 57 25.0
Denver, Colorado 64 36 17.8
Detroit, Michigan 59 42 15.0

Count of number of cities 14 Check_Sum

Average High (F) 66
Minimum Low (F) 36

Product of the values above 33264 Question 2

End of Section
Celsius = (Fahrenheit - 32) * (5/9)

Low (C)
11.7
15.0
7.2
11.7
6.7
4.4
9.4
5.0
6.1
6.1
6.7
13.9
2.2
5.6

2294 Question 3
Section 2 - Sorting and Arrays

Location High_F Low_F High_C Low_C

San Antonio, Texas 80 59 27 15
Virginia Beach, Virginia 68 52 20 11
Portland, Oregon 63 45 17 7
Boston, Massachusetts 59 44 15 7
New Orleans, Louisiana 78 61 26 16
Atlanta, Georgia 72 53 22 12
Austin, Texas 80 59 27 15
Denver, Colorado 64 36 18 2
Phoenix, Arizona 87 63 31 17
Minneapolis, Minnesota 55 37 13 3
San Francisco, California 64 51 18 10
New York, New York 62 48 17 9
Buffalo, New York 56 40 14 5
Tampa, Florida 82 65 28 18
Cleveland, Ohio 60 43 15 6
Dallas, Texas 77 57 25 14
Birmingham, Alabama 74 53 23 12
Indianapolis, Indiana 63 44 17 7
Miami, Florida 84 70 29 21
Houston, Texas 80 60 27 16
San Jose, California 71 50 22 10
Pittsburgh, Pennsylvania 61 42 16 6
Kansas City, Missouri 66 48 19 9
St. Louis, Missouri 66 48 19 9
Sacramento, California 74 48 23 9
Providence, Rhode Island 61 43 16 6
Columbus, Ohio 63 44 17 7
Nashville, Tennessee 70 49 21 9
Hartford, Connecticut 61 40 16 5
Orlando, Florida 83 63 28 17
Baltimore, Maryland 65 45 18 7
Raleigh, North Carolina 72 50 22 10
Louisville, Kentucky 68 49 20 9
Salt Lake City, Utah 64 42 18 5
Richmond, Virginia 70 48 21 9
Washington, DC 67 50 19 10
Charlotte, North Carolina 71 49 22 9
Detroit, Michigan 59 42 15 5
Rochester, New York 57 39 14 4
Oklahoma City, Oklahoma 72 51 22 10
Jacksonville, Florida 79 58 26 14
Seattle, Washington 61 46 16 8
Riverside, California 81 53 27 11
Milwaukee, Wisconsin 56 40 13 5
Memphis, Tennessee 73 54 23 12
Los Angeles, California 75 56 24 13
San Diego, California 70 58 21 14
Las Vegas, Nevada 80 59 27 15
Philadelphia, Pennsylvania 65 47 18 8
Cincinnati, Ohio 65 43 18 6
Chicago, Illinois 59 41 15 5

Section 3 - Transpose and more arrays

Check Sums 38940 35027
Question 4 Question 5

Rank of
Helper Location: Location of Helper Index
Index COUNTIF(S) Value (MATCH function)
1 43 6
2 50 7
3 35 31
4 5 17
5 28 4
6 1 13
7 2 37
8 13 51
9 33 50
10 26 15
11 45 27
12 29 16
13 6 8
14 49 38
15 10 29
16 12 20
17 4 18
18 17 41
19 24 23
20 16 48
21 46 46
22 34 33
23 19 45
24 48 19
25 41 44
26 36 10
27 11 28
28 27 5
29 15 12
30 31 40
31 3 30
32 37 49
33 22 9
34 42 22
35 38 3
36 51 26
37 7 32
38 14 35
39 40 43
40 30 39
41 18 25
42 47 34
43 39 1
44 25 47
45 23 11
46 21 21
47 44 42
48 20 24
49 32 14
50 9 2
51 8 36
 4698
Question 6 Question 7

5 largest High (F)

Final Location Sort Order (INDEX function) temperatures
Atlanta, Georgia 1 87
Austin, Texas 2 84
Baltimore, Maryland 3 83
Birmingham, Alabama 4 82
Boston, Massachusetts 5 81
Buffalo, New York
Charlotte, North Carolina Sum 417
Chicago, Illinois
Cincinnati, Ohio
Cleveland, Ohio Question 8
Columbus, Ohio Sum of [High_C * Low_C] for all locations with a Low_C less
Dallas, Texas 3294
Denver, Colorado
Detroit, Michigan
Hartford, Connecticut
Houston, Texas
Indianapolis, Indiana
Jacksonville, Florida
Kansas City, Missouri
Las Vegas, Nevada
Los Angeles, California
Louisville, Kentucky
Memphis, Tennessee
Miami, Florida
Milwaukee, Wisconsin
Minneapolis, Minnesota
Nashville, Tennessee
New Orleans, Louisiana
New York, New York
Oklahoma City, Oklahoma
Orlando, Florida
Philadelphia, Pennsylvania
Phoenix, Arizona
Pittsburgh, Pennsylvania
Portland, Oregon
Providence, Rhode Island
Raleigh, North Carolina
Richmond, Virginia
Riverside, California
Rochester, New York
Sacramento, California
Salt Lake City, Utah
San Antonio, Texas
San Diego, California
San Francisco, California
San Jose, California
Seattle, Washington
St. Louis, Missouri
Tampa, Florida
Virginia Beach, Virginia
Washington, DC
] for all locations with a Low_C less than 10
Section 3 - Transpose and more arrays

Towns
Alphaville
Betaburg
Charliefield
Deltatown
Echopolis
These have the name "Towns"

Distance table (miles)

Town 1 Town 2 Distance
Alphaville Alphaville 0 Question 9
Alphaville Betaburg 140 Question 10
Alphaville Charliefield 300 Question 11
Alphaville Deltatown 214
Alphaville Echopolis 78
Betaburg Alphaville 140 Row / Column counter
Betaburg Betaburg 0
Betaburg Charliefield 365 1
Betaburg Deltatown 320 2
Betaburg Echopolis 205 3
Charliefield Alphaville 300 4
Charliefield Betaburg 365 5
Charliefield Charliefield 0
Charliefield Deltatown 217
Charliefield Echopolis 348
Deltatown Alphaville 214
Deltatown Betaburg 320
Deltatown Charliefield 217
Deltatown Deltatown 0
Deltatown Echopolis 191
Echopolis Alphaville 78
Echopolis Betaburg 205
Echopolis Charliefield 348
Echopolis Deltatown 191
Echopolis Echopolis 0

End of section
 Alphaville Betaburg Charliefield
Alphaville 0 140 300
Betaburg 140 0 365
Charliefield 300 365 0
Deltatown 214 320 217
Echopolis 78 205 348

Check Sums
91256
2672
3766

w / Column counter 1 2 3
Alphaville Betaburg Charliefield
Alphaville 0 140 300
Betaburg 0 0 365
Charliefield 0 0 0
Deltatown 0 0 0
Echopolis 0 0 0
Deltatown Echopolis
214 78
320 205
217 348
0 191
191 0

4 5
Deltatown Echopolis
214 78
320 205
217 348
0 191
0 0