Greatest to smallest

[Dug]

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!!

 

[Cutter]

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

 

[bpeltzer]

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

 

[Ragdyer]

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))