Index and Match Formulas

[ralph76]

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

[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD="align: center"]Hand No.[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]Susan[/TD]
[TD="align: center"]Nick[/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]Nancy[/TD]
[TD="align: center"]Sarah[/TD]
[TD="align: center"]Mary[/TD]
[TD="align: center"]Robert[/TD]
[TD="align: center"]Math Check[/TD]
[TD="align: center"]Game Points[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Error[/TD]
[TD="align: center"]---[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]-10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

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

 

[Eric W]

That formula seems to be a bit overdesigned. Wouldn't

=IF(J2="Error","---",MAX(B2:I2))

work for you?

 

[ralph76]

Wow, Eric, that is PERFECT! Honestly, I never really understood why I needed all those Index and Match functions. This is so much simpler and understandable. Thank you very much!

 

[Eric W]

Glad to help!

 

[ralph76]

Sorry, one final question. Is there a way to have the cells in column K (Game Points) show as Blanks until the scores are entered in range B:I?

 

[Eric W]

Try:

=IFERROR(IF(J2="Error","---",LARGE(B2:I2,1)),"")

 

[ralph76]

PERFECT! Thanks again, Eric. I truly appreciate all your help.

 

[Eric W]

You're welcome! :wink: