P
Pete
Hi to all
[Apologies if this post has already been attended to.
After posting the question, I could not download any headers and hence could
not see if my question was indeed posted (or answered). I tried twice so it
might have appeared twice!
Then due to a glitch at the service providers, all postings from Saturday
afternoon till Monday morning were lost including my post and possible
responses.
So please could somebody help me. If there were any responses, please
repost or send to me ([email protected])]
If not seen before, here is the original query:
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 across 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
[Apologies if this post has already been attended to.
After posting the question, I could not download any headers and hence could
not see if my question was indeed posted (or answered). I tried twice so it
might have appeared twice!
Then due to a glitch at the service providers, all postings from Saturday
afternoon till Monday morning were lost including my post and possible
responses.
So please could somebody help me. If there were any responses, please
repost or send to me ([email protected])]
If not seen before, here is the original query:
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 across 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