SOLVED

Help with an IF AND formula

Copper Contributor

I’m a relatively inexperienced user and I am trying to write a formula to analyze golf SKINS but have stumbled and need help with the formula. Would appreciate any assistance in editing the following;

 

 =IF(AND($B32>18,$B32-18>=C$31,ScoreCard!C4-2,IF(AND($B32>18,$B32-18<C$31,ScoreCard!C4-1,IF(AND($B32<0,$B32+18<C$31,ScoreCard!C4+1,IF(C$31<=$B32,ScoreCard!C4-1,ScoreCard!C4)))))))

 

Where 

    $B32 equals player's handicap

    C$31 equals Hole handicap (C31-K31 for front 9 and N31-V31 for back 9)

    ScoreCard!C4 equals the hole score (C4-T4)

3 Replies
best response confirmed by dmac0070 (Copper Contributor)
Solution

@dmac0070 , working with nested IF and other long formulas it's better to use some formatting. You may Alt+Enter in formula bar, or copy/paste formula to/from Notepad, whatever - but that helps.

In you case the syntax could be

=IF(
   AND($B32>18,$B32-18>=C$31),
   ScoreCard!C4-2,
   IF(
      AND($B32>18,$B32-18<C$31),
      ScoreCard!C4-1,
      IF(
         AND($B32<0,$B32+18<C$31),
         ScoreCard!C4+1,
         IF(
            C$31<=$B32,
            ScoreCard!C4-1,
            ScoreCard!C4)
      )
   )
)

Perhaps it could be simplified more from content point of view, but it's need to play with any sample for that.

@Sergei Baklan 

 

Thanks for the quick response, Sergie. The brackets were in the wrong place!!!! 

 

I thank you for donating your time and effort to help out the inexperienced. It is greatly appreciated.

@dmac0070 , you are welcome, glad to help

1 best response

Accepted Solutions
best response confirmed by dmac0070 (Copper Contributor)
Solution

@dmac0070 , working with nested IF and other long formulas it's better to use some formatting. You may Alt+Enter in formula bar, or copy/paste formula to/from Notepad, whatever - but that helps.

In you case the syntax could be

=IF(
   AND($B32>18,$B32-18>=C$31),
   ScoreCard!C4-2,
   IF(
      AND($B32>18,$B32-18<C$31),
      ScoreCard!C4-1,
      IF(
         AND($B32<0,$B32+18<C$31),
         ScoreCard!C4+1,
         IF(
            C$31<=$B32,
            ScoreCard!C4-1,
            ScoreCard!C4)
      )
   )
)

Perhaps it could be simplified more from content point of view, but it's need to play with any sample for that.

View solution in original post