Rick,

I'm aware the OP said ties get 1/2 the value indicating no 3 way ties but
may this mod to make it more generic

=RANK(L1,L$1:L$8,1)+ROUND((COUNTIF(L$1:L$8,L1)1)/COUNTIF(L$1:L$8,L1),2)

Mike

"Rick Rothstein" wrote:

Actually, this formula is a little simpler...

=RANK(K2,K$1:K$8,1)+(COUNTIF(K$1:K$8,K2)1)/2

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Try this formula...

=(2*RANK(K1,K$1:K$8,1)+(COUNTIF(K$1:K$8,K1)1))/2

changing the ranges to match your actual conditions, of course.

--
Rick (MVP - Excel)


"sherbrooke" wrote in message
...

I have rows of figures over a number of columns, starting at column A,
with totals for each row in column K. I then want to allocate values
from 1 to 8 to each of the rows, in column L, with 8 to the highest
total and 1 to the lowest total, if 2 rows are the same total they each
receive half of the combined figures, as per the examples below:-

(This is a very simple example of what I use, in reality there are some
24 columns and 16 rows, where the values are from 1 to 16 rather than 1
to 8)

ABCDE FG H I J K L
Row 1 = 5 6 6 2 4 2 4 3 2 6 = 40 -- 8
Row 2 = 5 3 3 6 0 2 0 3 2 4 = 28 -- 2
Row 3 = 1 0 0 4 2 6 6 0 6 4 = 29 -- 3.5
Row 4 = 1 3 3 0 6 2 2 6 2 6 = 31 -- 5.5
Row 5 = 1 0 0 4 2 4 2 3 4 0 = 20 -- 1
Row 6 = 1 3 3 0 6 4 6 3 4 2 = 32 -- 7
Row 7 = 5 6 6 2 4 0 0 6 0 2 = 31 -- 5.5
Row 8 - 5 3 3 6 0 4 4 0 4 0 = 29 -- 3.5

What I require is a formula which will automatically insert the
appropriate value, 1 to 8 in the example above.

I would be most grateful for any suggestions.
--
JohnD