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