Top 3 performers

[Guest]

How can I identify, from a list, the top three customers who sold a vehicle for the highest amount?


Thanks

Adam

 

[Bob Phillips]

Suppose you have names in column A, amounts in column B, then

=INDEX(A:A,MATCH(LARGE(B:B,1),B:B,0),1)

and then
=INDEX(A:A,MATCH(LARGE(B:B,2),B:B,0),1)
=INDEX(A:A,MATCH(LARGE(B:B,3),B:B,0),1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

How can I identify, from a list, the top three customers who sold a

vehicle for the highest amount?

 

[Niek Otten]

Hi Adam,

Let's assume your sales figures are in A1:A7.
Inser a new column B, with this formula:

=RANK(A1,$A$1:$A$7)

This gives you the relative positions in the list. Now you just have to
extract the numbers one, two and three:

=INDEX($A$1:$A$7,MATCH(1,$B$1:$B$7))
=INDEX($A$1:$A$7,MATCH(2,$B$1:$B$7))
=INDEX($A$1:$A$7,MATCH(3,$B$1:$B$7))


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

How can I identify, from a list, the top three customers who sold a

vehicle for the highest amount?

 

[Jazzer]

Hi,

Possibly the easies way to do this is by conditional formatting.

1. Select the area where the sales figures are.

2. Go to Format -> Conditional Formatting

3. Change the Conditon 1 to "Formula is"

4. Type in the adjacent box

=IF(B2>=LARGE($B$2:$B$10,3),1,0)

where the B2 is the uppermost cell of the sales area and $B$2:$B$13
is the sales range.

5. Press Format... button and do some formatting. For example change
the font style as bold.


If you want to separate the TOP 3 sales persons, then it gets a bit
more tricky. I have the formula to do it in normal situation, but I
don't know how to do it in a tie situation.

Let's say, you have the sales persons in A2:A10 and their sales
figures in B2:B10. To find out the Top salesman use formula:

=INDEX($A$2:$A$10,MAX(IF($B$2:$B$10=LARGE($B$2:$B$10,1),ROW(INDIRECT("2:10"))-1,0)))

This is an array formula so you have to accept it by pressin Ctrl,
Shift & Enter simultaniously. To find out the Top 2 and 3, change the
number "1" inside the LARGE function to 2 or 3.

If your list starts from some other row than 2, change the -1 at the
end of the formula, so that you allways substarct (the starting row -
one).

As I sayed, this doesn't work correctly in a tie situation. Maybe
some bigger brain than me can help you with that.

- Asser

 

[David McRitchie]

Usually salesmen sell, and customers purchase.

Take a look at LARGE, MATCH, and INDEX Worksheet Functions in HELP.

#ID Sales
Dan 49,999.99 75,000.00 3 Joe *same*
Joe 75,000.00 75,000.00 3 Joe *same*
Moe 75,000.00 49,999.99 2 Dan
Pat 27,000.00 27,000.00 5 Pat
Sal 19,900.00 19,900.00 6 Sal
Sam 14,000.00 14,000.00 7 Sam

D2: =LARGE(B:B,1)
E2: =MATCH(D2,B:B,0)
F2: =INDEX(A:A,MATCH(D2,B:B,0))

Someone has a webpage that introduces a small error
so that you won't have duplicates for the same number
so that you would see Joe and Moe rather than Joe twice. --

 

[Guest]

OK one step further is needed sry, me spreadsheet is setup out as belo

Model Dealer Pric
Ford Focus Auto1 10,00
Ford escort Auto 2 10,00
Ford Focus Auto2 10,00
Ford Focus Auto 3 10,00
etc etc et

So can I see by each model which dealer sold for the highest price and then the price sold for so that I can select a vehicle and have a top 3 list of the dealers and max price sold for

If you could help I would greatly apprepriate this

Thanks
Adam

 

[Bob Phillips]

Niek,

Although your formula didn't work for me, missing value in MATCH I think, I
think it will suffer the same problem as mine if a value in the top 3 is
repeated.

I couldn't see how to fix mine, but , again assuming names in column A and
values in column B, yours us easily fixed with a RANK formula of

=RANK(B1,$B$1:$B$7)+COUNTIF($B$1:B1,B1)-1

in column C, and the lookup becomes

=INDEX($A$1:$A$7,MATCH(ROW(A1),$C$1:$C$7,0))

I added Row (A1) instead of 1 so that it can copy down without modification.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

I've worked out how to show the highest sold vehicle, and show the dealer and the price but how can I select a vehicle and then it shows me the high value that was sold for and by who and then the 2nd and 3rd.

 

[Guest]

Can anyone help pls

----- Adam wrote: ----

I've worked out how to show the highest sold vehicle, and show the dealer and the price but how can I select a vehicle and then it shows me the high value that was sold for and by who and then the 2nd and 3rd.

 

[David McRitchie]

perhaps
http://www.mvps.org/dmcritchie/excel/vlookup.htm#double

if that doesn't help along with my previous example, you will have
to post what your table looks like (in text, of course).

The correction for duplicate entries with MATCH, LARGE, INDEX
can be seen in Bob Phillips' reply using RANK in
http://google.com/groups?threadm=#[email protected]

and looking back in this thread he also solved the problem in this thread
in his second reply also using RANK.

Can anyone help pls?

----- Adam wrote: -----

I've worked out how to show the highest sold vehicle, and show the dealer and the price but how can I select a vehicle and

then it shows me the high value that was sold for and by who and then the 2nd and 3rd.