MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula/Method to find who's got top sales (In my example).


Posted by GLITZ on January 06, 2001 3:42 PM

I have 3 columns. Employee Number, Average, Sales
(A)(B)(C)

I have 2 awards to determine:top sales and 2nd place sales.


If only one person(employee number)has the top sales amount they are of course top. Same with 2nd.

If more than one employee has the highest number of sales the actual top sales award goes to the one with the highest average and 2nd sales is awarded to the employee with the 2nd highest average.

If top sales is awarded to one person and there is a tie for 2nd sales the employee with the highest average (tied people only) receives the 2nd place award.

Any input on how to create an output for my awards?


Thank You!


Posted by Ed on January 06, 2001 4:25 PM


Not sure of a formula but since you only have 2 awards one method is to sort you data. Click on Data, Sort by Sales descending then by Average descending. This will put the top sales on top of your list and if there is a tie it puts the one with the tops sales and highest average on top. So with this method you should have your winners in the first 2 rows.

Posted by Mark W. on January 06, 2001 4:45 PM

Assuming that you reserved row 1:1 for column headers enter {=COUNT($C:$C)-SUM((C2+B2/MAX($B:$B)>$C$2:$C$5+$B$2:$B$5/MAX($B:$B))+0)} into cell D2 and copy down. Note: This is an array formula that must be entered using Shift+Ctrl+Enter.

Posted by Mark W. on January 06, 2001 4:51 PM

I should have mentioned that this formula was developed on a data set with only 4 employees. You'll need to adjust the references to column C and B as more rows are added to the data set.

Posted by GLITZ on January 06, 2001 6:39 PM

1)
That seems to only count the number of values in row C
2)
Array's are kinda new to me but I think I'm doing what you said.....

Am I missing something?


Posted by Mark W. on January 06, 2001 6:48 PM

Does the formula displayed in the formula bar look like:

=COUNT($C:$C)-SUM(($C2+$B2/MAX($B:$B)>$C$2:$C$5+$B$2:$B$5/MAX($B:$B))+0)

or

{=COUNT($C:$C)-SUM(($C2+$B2/MAX($B:$B)>$C$2:$C$5+$B$2:$B$5/MAX($B:$B))+0)}

If it looks like the 1st one above then I'm betting that you didn't enter it
using Shift+Ctrl+Enter. When it's entered correctly if should look like the
2nd one above.

If it does look like the 2nd one then I'm guessing that you entered a single
array formula into all the cells on all rows of column D. If so, clear all
the cells; select only cell D2; click on the formula bar; paste the formula;
hold down the Shift and Ctrl keys; and press Enter.

Posted by GLITZ on January 06, 2001 7:13 PM

=COUNT($C:$C)-SUM(($C2+$B2/MAX($B:$B)>$C$2:$C$5+$B$2:$B$5/MAX($B:$B))+0) or {=COUNT($C:$C)-SUM(($C2+$B2/MAX($B:$B)>$C$2:$C$5+$B$2:$B$5/MAX($B:$B))+0)} If it looks like the 1st one above then I'm betting that you didn't enter it

That's Awsome! I still haven't figured out %100 how it works, but it sure does work!
Somewhere else in the work book I want to use a lookup and use the top sales and the 2nd place sales people....i'll just have the lookup find 1 (for top sales) and find 2 (for 2nd) to retrieve my info.

Question about the following:
=count($C:$C) <--when in column d

Does this count the number of times the value in the c cell appears in the c cloumn???

Posted by Mark W. on January 06, 2001 7:17 PM

By the way, here's an enhanced reformulation so you
don't have to adjust the formula for the number of
rows (employees).

{=COUNT($C:$C)-SUM(($C2+$B2/MAX($B:$B)>OFFSET($C$1,1,,COUNT($C:$C))+OFFSET($B$1,1,,COUNT($B:$B))/MAX($B:$B))+0)}

The same instructions apply:

1. Select cell D2.
2. Paste "=COUNT($C:$C)-SUM(($C2+$B2/MAX($B:$B)>OFFSET($C$1,1,,COUNT($C:$C))+OFFSET($B$1,1,,COUNT($B:$B))/MAX($B:$B))+0)"
into the formula bar.
3. Hold the [Shift] and [Ctrl] keys the press [Enter]
4. Copy the formula in cell D2 down to your last row of data

I'm curious if someone asked you to explain how this formula
works... could you? If not, do you want to understand it?

Posted by GLITZ on January 06, 2001 7:24 PM

I'll import this most recent update to my test sheet.

I'm taking your formula apart.....


It would be great if you could provide me with some insight on how you designed this formula..

Thank you for your time.


Posted by Mark W. on January 06, 2001 7:26 PM

Okay, we're on the same wave length now. In case
you missed it I posted an enhanced reformulation
as a follow-up to your earlier posting for help.

COUNT($C:$C) merely determines how many values
there are. I'd love to help you understand this
solution better, but right now I've got to catch
up with my buddies -- were going to see "Traffic".

I'll be around tomorrow. Maybe we can disect this
"monstrosity" then...