G
Guest
How can I identify, from a list, the top three customers who sold a vehicle for the highest amount?
Thanks
Adam
Thanks
Adam
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
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)
=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)
vehicle for the highest amount?Adam said:How can I identify, from a list, the top three customers who sold a
N
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
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
vehicle for the highest amount?Adam said:How can I identify, from a list, the top three customers who sold a
J
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
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
D
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. --
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. --
G
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
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
B
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)
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)
G
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.
G
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.
----- 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.
D
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.
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.
then it shows me the high value that was sold for and by who and then the 2nd and 3rd.Adam said: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