Logo
Home Study Material Contact Search
AI Chat FREE
  1. business
  2. accounting
  3. client total purchases in prior year average order

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

ClientTotal Purchases in Prior YearAverage OrderCustomer Rank By PurchasesCompany NameAverage Annual Sales
A                 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,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!

Other questions asked by students

Q

After being rejected for employment, Kim Kelly learns that the Bellevue Credit Company has hired only...

Advance Math
Q

One of Matt's favourite brands is offering a deal on its’ hooded jackets, which is sold...

Economics
Q

Shadee Corp. expects to sell 550 sun visors in May and 320 in June. Each visor...

Accounting
Q

II The following two way table shows students who went on a school trip to...

Statistics
Q

At the beginning of Year 2, Oak Consulting had the following normal...

Accounting
Q

19) The Tree Shoppe reported the following information: Budgeted revenues $10,000 Breakeven revenues 4,000 Budgeted...

Accounting
Q

Gonzalez Company acquired $200,000 of Walker Co., 6% bonds on May 1 at their face...

Accounting
Q

The simple version of the CAPM assumes that investors have a single period time horizon....

Finance
Q

You made an investment of $13,500 into an account that paid you an annual interest...

Finance
Q

On January 1, 2021, Majestic Mantles leased a lathe from Equipment Leasing under a finance...

Accounting
Q

Q5 ch 10 Ethics Problem Daane Dennison is a...

Finance
Q

Profitability Index P8-14. Evaluate the following three projects, using the profitability index. Assume a cost...

Finance