help with formula please

  • Thread starter Thread starter John
  • Start date Start date
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
 
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
Click to expand...


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
 
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
Click to expand...
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
 
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
Click to expand...

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
 
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
Click to expand...

Yup... thanks much.
John
 
Back
Top