I this is actually in an excel spread sheet but I couldnt get it to paste in cor
ID: 2340220 • Letter: I
Question
I this is actually in an excel spread sheet but I couldnt get it to paste in correctly. I need to know the formulas for the hlookup. It is copied the exact way its laid out in the spead sheet. PLEASE help!
Client Total Purchases in Prior Year Average Order Customer Rank By Purchases Company Name Average Annual Sales A 2,379 1,190 A (use hlookup formula here) B 72,560 6,596 E (use hlookup formula here) C 10,366 5,183 M (use hlookup formula here) D 77,745 7,068 E 11,106 5,553 F 83,295 7,572 G 11,899 5,950 H 89,243 8,113 I 12,749 6,375 J 95,618 8,693 K 13,660 6,830 L 102,450 9,314 M 14,636 7,318 N 109,770 9,979 O 15,681 7,841 P 117,608 10,692 Average 44,121 The assignment: (1) Use the attached document and dataset to do the following: a. In the area indicated on the spreadsheet create the hlookup formula to find the average annual sales for companies A, E, and M b. Find the median total purchases for the company’s customers. c. Complete the column ranking the customers from high (highest = #1) to low based on annual sales to those customers.Explanation / Answer
B
C
D
E
F
G
H
I
J
K
L
M
N
2
client
Total Purchases in Prior Year
Average Order
Customer Rank By Purchases
Company Name
Average Annual Sales
remarks
3
A
2379
1,190
16
A
2379
VLOOKUP(G3,B3:C18,2,TRUE)
4
B
72560
6,596
8
E
11106
VLOOKUP(G4,B3:C18,2,TRUE)
5
C
10366
5,183
15
M
14636
VLOOKUP(G5,B3:C18,2,TRUE)
6
D
77745
7,068
7
remarks in formula
VLOOKUP(Company Name ,Data Range,2,TRUE)
2 used for second colmun in data range
7
E
11106
5,553
14
HLOOKUP used in Horizontal data format. But here vertical data provided, so we can use VLOOKUP Function for Vertical data.
8
F
83295
7,572
6
9
G
11899
5,950
13
10
H
89243
8,113
5
11
I
12749
6,375
12
12
J
95618
8,693
4
13
K
13660
6,830
11
14
L
102450
9,314
3
15
M
14636
7,318
10
16
N
109770
9,979
2
17
O
15681
7,841
9
18
P
117608
10,692
1
19
20
Median
44121
21
remarks
MEDIAN(C3:C18)
B
C
D
E
F
G
H
I
J
K
L
M
N
2
client
Total Purchases in Prior Year
Average Order
Customer Rank By Purchases
Company Name
Average Annual Sales
remarks
3
A
2379
1,190
16
A
2379
VLOOKUP(G3,B3:C18,2,TRUE)
4
B
72560
6,596
8
E
11106
VLOOKUP(G4,B3:C18,2,TRUE)
5
C
10366
5,183
15
M
14636
VLOOKUP(G5,B3:C18,2,TRUE)
6
D
77745
7,068
7
remarks in formula
VLOOKUP(Company Name ,Data Range,2,TRUE)
2 used for second colmun in data range
7
E
11106
5,553
14
HLOOKUP used in Horizontal data format. But here vertical data provided, so we can use VLOOKUP Function for Vertical data.
8
F
83295
7,572
6
9
G
11899
5,950
13
10
H
89243
8,113
5
11
I
12749
6,375
12
12
J
95618
8,693
4
13
K
13660
6,830
11
14
L
102450
9,314
3
15
M
14636
7,318
10
16
N
109770
9,979
2
17
O
15681
7,841
9
18
P
117608
10,692
1
19
20
Median
44121
21
remarks
MEDIAN(C3:C18)