Hi,

The format approach was my first thought but then none-ties end up with a
..00 decimal. Maybe it's me being picky:)

Mike

"Rick Rothstein" wrote:

No apologies necessary. I posted this response separately because I thought
if I went 3 posting levels deep, that the OP might not read that far
down.<g As for including the ROUND function, I figured the OP would use
Cell Format to restrict decimals if he wanted to do that (Custom Format of
0.0# for 2 decimal places maximum maybe).

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Apologies I didn't see you'd posted this when I responded to your other
post,
still think it's better with round to get rid of reccuring decimals

Mike

"Rick Rothstein" wrote:

Actually, of the formulas submitted so far (mine included), none of them
account for 3 or more way ties. This formula does...

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

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