help with formula please

J

John

This is for scoring duplicate bridge


essentially this:

Have five scores

1 40 1.5
2 100 3
3 40 1.5
4 400 4
5 -60 0

Want to award points from 0 to 4 as shown.

given second, what would a formula be in each cell of the third column
that would produce the score? ties are common and scores are given to 1
dec place by halves.

I have pretty much got nowhere with this so far.



Thanks for any help

John
 
L

Lars-Åke Aspelin

This is for scoring duplicate bridge


essentially this:

Have five scores

1 40 1.5
2 100 3
3 40 1.5
4 400 4
5 -60 0

Want to award points from 0 to 4 as shown.

given second, what would a formula be in each cell of the third column
that would produce the score? ties are common and scores are given to 1
dec place by halves.

I have pretty much got nowhere with this so far.



Thanks for any help

John


If your scores are in cells B1 to B5, try the following formula in
cell C1:

=5-RANK(B1,B$1:B$5)+(1-COUNTIF(B$1:B$5,B1))/2

Copy the formula down to cell C5.

Hope this helps / Lars-Åke
 
J

John

Lars-Åke Aspelin said:
If your scores are in cells B1 to B5, try the following formula in
cell C1:

=5-RANK(B1,B$1:B$5)+(1-COUNTIF(B$1:B$5,B1))/2

Copy the formula down to cell C5.

Hope this helps / Lars-Åke
Thanks

Works perfectly but can't copy paste the formula. There are 20 score
sheets on worksheet(1). When I paste to a cell on a different score
sheet the $'s don't change.

John
 
L

Lars-Åke Aspelin

Thanks

Works perfectly but can't copy paste the formula. There are 20 score
sheets on worksheet(1). When I paste to a cell on a different score
sheet the $'s don't change.

John

The $'s are only needed when you copy/autofill the formula from C1 to
C5. When you have done that, the $'s can be removed from the formulas
and the five cells C1 to C5 can the be copied to serve another set of
scores at some other place on the same or any other worksheet.

Hope this helps / Lars-Åke
 
J

John

Lars-Åke Aspelin said:
The $'s are only needed when you copy/autofill the formula from C1 to
C5. When you have done that, the $'s can be removed from the formulas
and the five cells C1 to C5 can the be copied to serve another set of
scores at some other place on the same or any other worksheet.

Hope this helps / Lars-Åke

Yup... thanks much.
John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top