Good afternoon,
Here is a sample spreadsheet for which my questions will be related:
A B C D E F G H I J K
<tbody>
</tbody>
I'm trying to update a spreadsheet that Mr.Excel Members helped me develop to keep track of each round of scoring of a card game. Here are the formulas I have, along with brief descriptions:
1. Column J (Math Check): IF(SUM(B2:I2)<>0,"Error","")
2. Column K (Game Points): IF(J2="Error","---",IFERROR(ABS(INDEX(B2:I2,MATCH(TRUE,INDEX((B2:I2<>0),0),0))),"")
Notes:
1. The sum total of all scores in each round must add up to zero, which is why I included the Math Check. Note: Hand #3 shows the outputs in columns J and K when an input error results in the scores not adding to zero.
2. The Game Points always equal the number of points in a hand. The reason for the Absolute Value function is if the first score encountered in the Index range is a negative (see Hands #1 and #3 ), it will be converted to a positive. All Game Points must be registered as a positive number.
Current Issue: At the time the Game Points formula was developed for me, I was unaware that zero scores were possible. Unfortunately, with the current Game Points formula, when zeros are input in the score ranges (see Hand #5 ) the formula in cell K6 returns a Blank, not a 0.
Question: Would someone be so kind as to show me how to modify the Game Points formula (or develop a new formula) to show 0s and not Blanks?
Many thanks,
Ralph
Here is a sample spreadsheet for which my questions will be related:
A B C D E F G H I J K
| Hand No. | John | Susan | Nick | Jane | Nancy | Sarah | Mary | Robert | Math Check | Game Points |
| 1 | -4 | 4 | 4 | -4 | 4 | |||||
| 2 | 6 | -6 | -6 | 6 | 6 | |||||
| 3 | -2 | 2 | -2 | -2 | Error | --- | ||||
| 4 | 10 | -10 | 10 | -10 | 10 | |||||
| 5 | 0 | 0 | 0 | 0 |
<tbody>
</tbody>
I'm trying to update a spreadsheet that Mr.Excel Members helped me develop to keep track of each round of scoring of a card game. Here are the formulas I have, along with brief descriptions:
1. Column J (Math Check): IF(SUM(B2:I2)<>0,"Error","")
2. Column K (Game Points): IF(J2="Error","---",IFERROR(ABS(INDEX(B2:I2,MATCH(TRUE,INDEX((B2:I2<>0),0),0))),"")
Notes:
1. The sum total of all scores in each round must add up to zero, which is why I included the Math Check. Note: Hand #3 shows the outputs in columns J and K when an input error results in the scores not adding to zero.
2. The Game Points always equal the number of points in a hand. The reason for the Absolute Value function is if the first score encountered in the Index range is a negative (see Hands #1 and #3 ), it will be converted to a positive. All Game Points must be registered as a positive number.
Current Issue: At the time the Game Points formula was developed for me, I was unaware that zero scores were possible. Unfortunately, with the current Game Points formula, when zeros are input in the score ranges (see Hand #5 ) the formula in cell K6 returns a Blank, not a 0.
Question: Would someone be so kind as to show me how to modify the Game Points formula (or develop a new formula) to show 0s and not Blanks?
Many thanks,
Ralph
Last edited:
