Excel Formula Help

dunnee8

I am doing up a leaderboard for me and friends for a number of rounds of golf. I want to be able to list our scores in e.g cells A1, A2, A3.... and the the lowest score will get 10pts populated beside their score in the adjacent cell. for example if A1=72, A2=76, A3=80 then when ive put these scores in automatically B1 will populate 10, B2=8 & B3=6. Looking for B1-B3 to locate the min value in A1-A3 and to insert the appropriate score.

Is this possible?

Diddy Kong

It is possible, easiest way would be to do a macro to perform the task for you.

nedzer2011

A macro would work but if you're not comfortable with VBA but the only formulae I can think of are a huge...

If theres only three of you:

=IF(A1=MAX($A$1:$A$3),10,IF(A1=MEDIAN($A$1:$A$3),8,IF(A1=MIN($A$1:$A$3),6)))

and drag down for A2 and A3.

If theres more than three (4 in this case) maybe try something like this:

=IF(A1=MAX($A$1:$A$3),10,IF((RANK(A1,$A$1:$A$3,1)=2),8,IF((RANK(A1,$A$1:$A$3,1)=3),6,IF(A1=MIN($A$1:$A$3),4))))

and drag down into A2,A3.

Theres probably a more straight forward method... like typing the values in manually!!:D

dunnee8

Manually is prob the best ha! Thanks

Spunj

A simple way to do this (takes just a second and requires no work) would be to put 10, 8 and 6 into A1, A2, A3.

Then just type the score into B1-B3 and name into C1-C3.

Select columns B and C and click on the sort icon. By default this will sort by column B, lining the lowest score up to the 10 and so on.