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

=IF(COUNTIF(A$1:A$8,A1)=1,RANK(A1,A$1:A$8,1),RANK( A1,A$1:A$8,1)+0.5)

"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

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

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

Hi,

One way would be

=RANK(K1,$K$1:$K$8,1)+(COUNT($K$1:$K$8)+1-RANK(K1,$K$1:$K$8,0)-RANK(K1,$K$1:$K$8,1))/2

Patrik




On Fri, 23 Jan 2009 19:11:48 +0000, 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.

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

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

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

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

forgot to mention that this is an array formula, after placing the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly
brackets wrap around the formula in the formula bar.

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"xlmate" wrote:


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

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

Hi,

Nice approach to the problem. How about this to make it cope with 3 way ties

=SUM(1*(L1=$L$1:$L$8))-ROUND((SUM(1*(L1=$L$1:$L$8))-1)/COUNTIF(L$1:L$8,L1),2)

Mike

"xlmate" wrote:

forgot to mention that this is an array formula, after placing the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly
brackets wrap around the formula in the formula bar.

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"xlmate" wrote:


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

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

Hi Mike

Simply excellent ;)

--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"Mike H" wrote:

Hi,

Nice approach to the problem. How about this to make it cope with 3 way ties

=SUM(1*(L1=$L$1:$L$8))-ROUND((SUM(1*(L1=$L$1:$L$8))-1)/COUNTIF(L$1:L$8,L1),2)

Mike

"xlmate" wrote:

forgot to mention that this is an array formula, after placing the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly
brackets wrap around the formula in the formula bar.

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"xlmate" wrote:


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

Just one zero after the decimal point with my custom format pattern <g, but
I do see your point.

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
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

I can't get your formula to work for 3 way ties :-(

I came up with this formula that I hope works for n-way ties.

=SUM(1*(L1=$L$1:$L$8))-(SUM(1*(L1=$L$1:$L$8))-1)/2

Lars-Åke


On Fri, 23 Jan 2009 12:38:11 -0800, Mike H
wrote:

Hi,

Nice approach to the problem. How about this to make it cope with 3 way ties

=SUM(1*(L1=$L$1:$L$8))-ROUND((SUM(1*(L1=$L$1:$L$8))-1)/COUNTIF(L$1:L$8,L1),2)

Mike

"xlmate" wrote:

forgot to mention that this is an array formula, after placing the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly
brackets wrap around the formula in the formula bar.

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"xlmate" wrote:


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

Lars

I almost hesitate to mention it given some of the stuff you have posted but
you did array enter it didn't you?

the formula also works for n way ties and adds a decimal for those ties. for
example a 3 way tie returns n.33 & a 4 way n.25

I think you need to look at your formula again. I get no decimals for 3 way
ties and n.5 for 4 way ties.

Mike

"Lars-Ã…ke Aspelin" wrote:

I can't get your formula to work for 3 way ties :-(

I came up with this formula that I hope works for n-way ties.

=SUM(1*(L1=$L$1:$L$8))-(SUM(1*(L1=$L$1:$L$8))-1)/2

Lars-Ã…ke


On Fri, 23 Jan 2009 12:38:11 -0800, Mike H
wrote:

Hi,

Nice approach to the problem. How about this to make it cope with 3 way ties

=SUM(1*(L1=$L$1:$L$8))-ROUND((SUM(1*(L1=$L$1:$L$8))-1)/COUNTIF(L$1:L$8,L1),2)

Mike

"xlmate" wrote:

forgot to mention that this is an array formula, after placing the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly
brackets wrap around the formula in the formula bar.

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"xlmate" wrote:


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

I did enter the formula as an array formula indeed.
But I must have misunderstood the problem and the meaning of the
phrase "combined figures". My apologies for that.

I understood it as it there was a tie, then the scores should be
divided equally among those involved. But the total sum of the scores
always be the same. So in the case of 8 values the sum of the scores
should always be 36. (8 to the highest, 7 to the second highest and so
on.)
And if there is a 2-tie then they both get (8+7)/2 = 7.5
If there is a 3-tie then they all get (8+7+6)/3 = 7 etc
That is why you don't get any decimals for a (2N+1)-way tie in my
formula.

Lars-Åke

On Fri, 23 Jan 2009 13:46:07 -0800, Mike H
wrote:

Lars

I almost hesitate to mention it given some of the stuff you have posted but
you did array enter it didn't you?

the formula also works for n way ties and adds a decimal for those ties. for
example a 3 way tie returns n.33 & a 4 way n.25

I think you need to look at your formula again. I get no decimals for 3 way
ties and n.5 for 4 way ties.

Mike

"Lars-Åke Aspelin" wrote:

I can't get your formula to work for 3 way ties :-(

I came up with this formula that I hope works for n-way ties.

=SUM(1*(L1=$L$1:$L$8))-(SUM(1*(L1=$L$1:$L$8))-1)/2

Lars-Åke


On Fri, 23 Jan 2009 12:38:11 -0800, Mike H
wrote:

Hi,

Nice approach to the problem. How about this to make it cope with 3 way ties

=SUM(1*(L1=$L$1:$L$8))-ROUND((SUM(1*(L1=$L$1:$L$8))-1)/COUNTIF(L$1:L$8,L1),2)

Mike

"xlmate" wrote:

forgot to mention that this is an array formula, after placing the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly
brackets wrap around the formula in the formula bar.

HTH
--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis









"xlmate" wrote:


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

Many thanks to everyone who has replied,

It seems that I have certainly got a solution to my problem, I will now
go away and try all of them out to determine which will meet my
requirements in the best way.

I am very grateful for all the suggestions.


I did enter the formula as an array formula indeed.
But I must have misunderstood the problem and the meaning of the
phrase "combined figures". My apologies for that.

I understood it as it there was a tie, then the scores should be
divided equally among those involved. But the total sum of the scores
always be the same. So in the case of 8 values the sum of the scores
should always be 36. (8 to the highest, 7 to the second highest and so
on.)
And if there is a 2-tie then they both get (8+7)/2 = 7.5
If there is a 3-tie then they all get (8+7+6)/3 = 7 etc
That is why you don't get any decimals for a (2N+1)-way tie in my
formula.

Lars-Åke

On Fri, 23 Jan 2009 13:46:07 -0800, Mike H
wrote:

Lars

I almost hesitate to mention it given some of the stuff you have posted but
you did array enter it didn't you?

the formula also works for n way ties and adds a decimal for those ties. for
example a 3 way tie returns n.33 & a 4 way n.25

I think you need to look at your formula again. I get no decimals for 3 way
ties and n.5 for 4 way ties.

Mike

"Lars-Åke Aspelin" wrote:

I can't get your formula to work for 3 way ties :-(

I came up with this formula that I hope works for n-way ties.

=SUM(1*(L1=$L$1:$L$8))-(SUM(1*(L1=$L$1:$L$8))-1)/2

Lars-Åke


On Fri, 23 Jan 2009 12:38:11 -0800, Mike H
wrote:

Hi,

Nice approach to the problem. How about this to make it cope with 3
way ties




====SUM(1*(L1=$L$1:$L$8))-ROUND((SUM(1*(L1=$L$1:$L$8))-1)/COUNTIF(L$1:L$8,L1),2)

Mike

"xlmate" wrote:

forgot to mention that this is an array formula, after placing
the formula,
press Ctrl, Shift and Enter all at once to enter, you will see curly
brackets wrap around the formula in the formula bar.

HTH
--
Your feedback is very much appreciate, pls click on the Yes
button below if
this posting is helpful.

Thank You

cheers, francis









"xlmate" wrote:


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

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

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





--
JohnD

In message , sherbrooke
writes
Many thanks to everyone who has replied,

It seems that I have certainly got a solution to my problem, I will now
go away and try all of them out to determine which will meet my
requirements in the best way.

I am very grateful for all the suggestions.

As a follow up to this thread, I have now tried each of the suggestions
with varying degrees of success. I comment below on each of the
suggested answers, the correct display was 7.5, 1.5, 3.5, 5,5, 1.5, 7.5,
5.5, 3.5.

I could not get Sean Timmons formula to work, when entered into L1:L8 it
returned 5.5, 5.5, 1.5, 1.5, 1.5, 1.5, 5.5, 5.5.

Rick Rothstein's first answer worked fine even with 'ties'. However I
couldn't get his second suggestion to work. When I put the formula in
L1:L8 it returned 1.5, 3.5, 5.5, 1.5, 7.5, 5.5, 2.5, #NA.

Patrik (Pathed) works fine

Rick Rothstein's third suggestion works fine.

Mike H - I could only get an error - Circular reference

xlmate - If I posted the answer in L1:L8 I got Circular Reference, if
posted in M1:M8 it returned 4.5, 4.5, 4.5, 4.5, 4.5, 4.5, 4.5, 4.5.

Mike H - second suggestion if posted in L1:L8 it returned a circular
ref. and if posted in M1:M8 it returned #DIV0!

Lars-Åke Aspelin - If posted in L1:L8 I got Circular Reference, if
posted in M1:M8 it returned 1, 1, 1, 1, 1, 1, 1, 1. If entered as an
array it returned 4.5, 4.5, 4.5, 4.5, 4.5, 4.5,4.5, 4.5.

I do hope that my comments are of some use or interest to all who
responded to my enquiry, I am most grateful to all of them and I do have
3 answers to my problem!

Many thanks once again.


--
JohnD

On Mon, 26 Jan 2009 16:15:27 +0000, sherbrooke
wrote:

In message , sherbrooke
writes
Many thanks to everyone who has replied,

It seems that I have certainly got a solution to my problem, I will now
go away and try all of them out to determine which will meet my
requirements in the best way.

I am very grateful for all the suggestions.

As a follow up to this thread, I have now tried each of the suggestions
with varying degrees of success. I comment below on each of the
suggested answers, the correct display was 7.5, 1.5, 3.5, 5,5, 1.5, 7.5,
5.5, 3.5.

I could not get Sean Timmons formula to work, when entered into L1:L8 it
returned 5.5, 5.5, 1.5, 1.5, 1.5, 1.5, 5.5, 5.5.

Rick Rothstein's first answer worked fine even with 'ties'. However I
couldn't get his second suggestion to work. When I put the formula in
L1:L8 it returned 1.5, 3.5, 5.5, 1.5, 7.5, 5.5, 2.5, #NA.

Patrik (Pathed) works fine

Rick Rothstein's third suggestion works fine.

Mike H - I could only get an error - Circular reference

xlmate - If I posted the answer in L1:L8 I got Circular Reference, if
posted in M1:M8 it returned 4.5, 4.5, 4.5, 4.5, 4.5, 4.5, 4.5, 4.5.

Mike H - second suggestion if posted in L1:L8 it returned a circular
ref. and if posted in M1:M8 it returned #DIV0!

Lars-Åke Aspelin - If posted in L1:L8 I got Circular Reference, if
posted in M1:M8 it returned 1, 1, 1, 1, 1, 1, 1, 1. If entered as an
array it returned 4.5, 4.5, 4.5, 4.5, 4.5, 4.5,4.5, 4.5.

I do hope that my comments are of some use or interest to all who
responded to my enquiry, I am most grateful to all of them and I do have
3 answers to my problem!

Many thanks once again.

My formula should be entered as an array formula in cell L1 and then
copied down. But please replace all L with K in the formula.

Like this:

=SUM(1*(K1=$K$1:$K$8))-(SUM(1*(K1=$K$1:$K$8))-1)/2

With the data of your orignal post (in K1:K8)
40
28
29
31
20
32
31
29
The result will be (in L1:L8)
8
2
3.5
5.5
1
7
5.5
3.5

Lars-Åke

On Mon, 26 Jan 2009 16:15:27 +0000, sherbrooke
wrote:

In message , sherbrooke
writes
Many thanks to everyone who has replied,

It seems that I have certainly got a solution to my problem, I will now
go away and try all of them out to determine which will meet my
requirements in the best way.

I am very grateful for all the suggestions.

As a follow up to this thread, I have now tried each of the suggestions
with varying degrees of success. I comment below on each of the
suggested answers, the correct display was 7.5, 1.5, 3.5, 5,5, 1.5, 7.5,
5.5, 3.5.

I could not get Sean Timmons formula to work, when entered into L1:L8 it
returned 5.5, 5.5, 1.5, 1.5, 1.5, 1.5, 5.5, 5.5.

Rick Rothstein's first answer worked fine even with 'ties'. However I
couldn't get his second suggestion to work. When I put the formula in
L1:L8 it returned 1.5, 3.5, 5.5, 1.5, 7.5, 5.5, 2.5, #NA.

Patrik (Pathed) works fine

Rick Rothstein's third suggestion works fine.

Mike H - I could only get an error - Circular reference

xlmate - If I posted the answer in L1:L8 I got Circular Reference, if
posted in M1:M8 it returned 4.5, 4.5, 4.5, 4.5, 4.5, 4.5, 4.5, 4.5.

Mike H - second suggestion if posted in L1:L8 it returned a circular
ref. and if posted in M1:M8 it returned #DIV0!

Lars-Åke Aspelin - If posted in L1:L8 I got Circular Reference, if
posted in M1:M8 it returned 1, 1, 1, 1, 1, 1, 1, 1. If entered as an
array it returned 4.5, 4.5, 4.5, 4.5, 4.5, 4.5,4.5, 4.5.

I do hope that my comments are of some use or interest to all who
responded to my enquiry, I am most grateful to all of them and I do have
3 answers to my problem!

Many thanks once again.

Also in the formulas from Sean Timmons, Mike H, and xlmate
you should enter in the L column and replace all A (or L) with K.
These formula all give the same result for non ties and 2-way ties,
but when it comes to 3-way ties we have some different interpretations
of what you expect. Comment if you like.

Hope this help / Lars-Åke

In message , Lars-Åke
Aspelin writes
On Mon, 26 Jan 2009 16:15:27 +0000, sherbrooke
wrote:

In message , sherbrooke
writes
Many thanks to everyone who has replied,

It seems that I have certainly got a solution to my problem, I will now
go away and try all of them out to determine which will meet my
requirements in the best way.

I am very grateful for all the suggestions.

As a follow up to this thread, I have now tried each of the suggestions
with varying degrees of success. I comment below on each of the
suggested answers, the correct display was 7.5, 1.5, 3.5, 5,5, 1.5, 7.5,
5.5, 3.5.

I could not get Sean Timmons formula to work, when entered into L1:L8 it
returned 5.5, 5.5, 1.5, 1.5, 1.5, 1.5, 5.5, 5.5.

Rick Rothstein's first answer worked fine even with 'ties'. However I
couldn't get his second suggestion to work. When I put the formula in
L1:L8 it returned 1.5, 3.5, 5.5, 1.5, 7.5, 5.5, 2.5, #NA.

Patrik (Pathed) works fine

Rick Rothstein's third suggestion works fine.

Mike H - I could only get an error - Circular reference

xlmate - If I posted the answer in L1:L8 I got Circular Reference, if
posted in M1:M8 it returned 4.5, 4.5, 4.5, 4.5, 4.5, 4.5, 4.5, 4.5.

Mike H - second suggestion if posted in L1:L8 it returned a circular
ref. and if posted in M1:M8 it returned #DIV0!

Lars-Åke Aspelin - If posted in L1:L8 I got Circular Reference, if
posted in M1:M8 it returned 1, 1, 1, 1, 1, 1, 1, 1. If entered as an
array it returned 4.5, 4.5, 4.5, 4.5, 4.5, 4.5,4.5, 4.5.

I do hope that my comments are of some use or interest to all who
responded to my enquiry, I am most grateful to all of them and I do have
3 answers to my problem!

Many thanks once again.

Also in the formulas from Sean Timmons, Mike H, and xlmate
you should enter in the L column and replace all A (or L) with K.
These formula all give the same result for non ties and 2-way ties,
but when it comes to 3-way ties we have some different interpretations
of what you expect. Comment if you like.

Hope this help / Lars-Åke

Thank you once again.
I have noted your comments and realise what I should have done with the
formulas I could not get working.
As long as the formula works with 2 and 3 way ties, it will suit my
purposes.

Thanks for your further advice, I appreciate it.

--
JohnD