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

Free

80.2K

Verified Solution

Question

Accounting

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 theattached document and dataset to do the following:
a.       In the area indicated on thespreadsheet create the hlookup formula to find the average annualsales for companies A, E, and M
b.      Find the median total purchasesfor the company’s customers.
c.       Complete the column rankingthe customers from high (highest = #1) to low based on annual salesto those customers.
I this is actually in an excel spread sheet but I couldnt get itto 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. PLEASEhelp!

Answer & Explanation Solved by verified expert

4.0 Ratings (788 Votes)

	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)

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

Transcribed Image Text

ClientTotal Purchases in Prior YearAverage OrderCustomer Rank By PurchasesCompany NameAverage Annual SalesA 2,379 1,190A(use hlookup formula here)B 72,560 6,596E(use hlookup formula here)C 10,366 5,183M(use hlookup formula here)D 77,745 7,068E 11,106 5,553F 83,295 7,572G 11,899 5,950H 89,243 8,113I 12,749 6,375J 95,618 8,693K 13,660 6,830L 102,450 9,314M 14,636 7,318N 109,770 9,979O 15,681 7,841P 117,608 10,692Average 44,121The assignment:(1) Use theattached document and dataset to do the following:a. In the area indicated on thespreadsheet create the hlookup formula to find the average annualsales for companies A, E, and Mb. Find the median total purchasesfor the company’s customers.c. Complete the column rankingthe customers from high (highest = #1) to low based on annual salesto those customers.I this is actually in an excel spread sheet but I couldnt get itto 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. PLEASEhelp!