Greatest to smallest
 

I have a Excel spread sheet of emplyees sales. For this example lets say that
cells A1 to A10 have emplyoee sales for the month. I need to calculate their
commissions but the commissions are based on the emplyee sales ranking. The
top seller gets 2% 2nd place gets 1% 3rd place gets 0.5% and the rest get
..25%. How can I have Excel calculate the biggest number then the 2nd biggest
then the 3rd biggest etc... Thank you!!

Greatest to smallest
 

Use the LARGE() function or the RANK() function

Using your A1:A10 example then:
=LARGE(A1:A10,1) yields the same result as =MAX(A1:A10,1)
=LARGE(A1:A10,2) yields the 2nd highest
=LARGE(A1:A10,3) yields the 3rd highest
etc.

If you want to have the results in an adjacent column then in B1 type:
=RANK(A1,$A$1:$A$10)
and copy it down to B10


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=490568

Greatest to smallest
 

Check the function help for RANK and/or LARGE. Given the way your data is
organized, I think RANK will be more helpful.

"Dug" wrote:

I have a Excel spread sheet of emplyees sales. For this example lets say that
cells A1 to A10 have emplyoee sales for the month. I need to calculate their
commissions but the commissions are based on the emplyee sales ranking. The
top seller gets 2% 2nd place gets 1% 3rd place gets 0.5% and the rest get
.25%. How can I have Excel calculate the biggest number then the 2nd biggest
then the 3rd biggest etc... Thank you!!

Greatest to smallest
 

With sales in A1:A10, enter this formula in B1, and copy down to get the
commissions for each sales value:

=IF(RANK(A1,$A$1:$A$10)3,A1*0.0025,A1*CHOOSE(RANK (A1,$A$1:$A$10),0.02,0.01,
0.005))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dug" wrote in message
...
I have a Excel spread sheet of emplyees sales. For this example lets say
that
cells A1 to A10 have emplyoee sales for the month. I need to calculate
their
commissions but the commissions are based on the emplyee sales ranking.
The
top seller gets 2% 2nd place gets 1% 3rd place gets 0.5% and the rest get
.25%. How can I have Excel calculate the biggest number then the 2nd
biggest
then the 3rd biggest etc... Thank you!!