help with formula please

[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

 

[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

 

[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

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

 

[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

 

[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

Yup... thanks much.
John