P
Pete Bircher
Hi to all
I would really like to enhance a formula:
In bridge (the game) the raw scores are ranked and a value assigned where
the top score will get the result of: (no of scores-1)*2 and the next will
get 2 less than the top. So for 9 raw scores you get 16, 14, 12, etc . . .
down to 4, 2, 0 as shown under the column headed SCORES (shared positions
share both score values)
Board Results SCORE Should be
1 500 16 4
1 -300 0 0
1 400 8 2
2 -200 3 1
2 450 14 4
2 -200 3 1
3 -50 6 0
3 430 11 3
3 430 11 3
In the column headed by "SCORE" I have the following formulae:
=(COUNTA(B$4:B$12,B4<>"")-RANK(B4,B$4:B$12))*2-COUNTIF(B$4:B$12,"="&TEXT(B4,"0.00"))-1
Up to now the formula has worked well as all the boards were all the same
number (say, all board 1 or all board 2 on separate sheets). However I now
wish to have a formula that applies accoss different boards (e.g boards 1, 2
and 3) all in the same column (A) and I want the formula to apply (match?)
only to similar board numbers giving the scores shown in the column headed
"Should Be" e.g. giving a score of 4, 2, or 0 (and again shared if tied)
My questions are:
Can MATCH and/or INDEX be incorporated into my formula and, if so, how?
Is there a better way of achieving my objective? (other functions?)
Is my original formula unnecessarily complex? Can I improve it?
Thanks in advance,
Peter
Kwazulu-Natal, SA
I would really like to enhance a formula:
In bridge (the game) the raw scores are ranked and a value assigned where
the top score will get the result of: (no of scores-1)*2 and the next will
get 2 less than the top. So for 9 raw scores you get 16, 14, 12, etc . . .
down to 4, 2, 0 as shown under the column headed SCORES (shared positions
share both score values)
Board Results SCORE Should be
1 500 16 4
1 -300 0 0
1 400 8 2
2 -200 3 1
2 450 14 4
2 -200 3 1
3 -50 6 0
3 430 11 3
3 430 11 3
In the column headed by "SCORE" I have the following formulae:
=(COUNTA(B$4:B$12,B4<>"")-RANK(B4,B$4:B$12))*2-COUNTIF(B$4:B$12,"="&TEXT(B4,"0.00"))-1
Up to now the formula has worked well as all the boards were all the same
number (say, all board 1 or all board 2 on separate sheets). However I now
wish to have a formula that applies accoss different boards (e.g boards 1, 2
and 3) all in the same column (A) and I want the formula to apply (match?)
only to similar board numbers giving the scores shown in the column headed
"Should Be" e.g. giving a score of 4, 2, or 0 (and again shared if tied)
My questions are:
Can MATCH and/or INDEX be incorporated into my formula and, if so, how?
Is there a better way of achieving my objective? (other functions?)
Is my original formula unnecessarily complex? Can I improve it?
Thanks in advance,
Peter
Kwazulu-Natal, SA