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

OR
to just show the commission in the adjacent cell you could do this:
=CHOOSE(RANK(A1,$A$1:$A$10),2,1,0.5,0.25,0.25,0.25,0.25,0.25,0.25,0.25)

[bpeltzer]

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

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dug" <[email protected]> wrote in message
news:[email protected]...
> 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!!