Formula required.
assuming that your data start in row 1, try this formula in L1 and copy down
=SUM(1*(L1=$L$1:$L$8))-(SUM(1*(L1=$L$1:$L$8))-1)/2
Does this do what you what?
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.
Thank You
cheers, francis
"sherbrooke" wrote:
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
|