Formula required.

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