SOLVED
Home

Help with an IF AND formula

%3CLINGO-SUB%20id%3D%22lingo-sub-401427%22%20slang%3D%22en-US%22%3EHelp%20with%20an%20IF%20AND%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401427%22%20slang%3D%22en-US%22%3E%3CP%3EI%E2%80%99m%20a%20relatively%20inexperienced%20user%20and%20I%20am%20trying%20to%20write%20a%20formula%20to%20analyze%20golf%20SKINS%20but%20have%20stumbled%20and%20need%20help%20with%20the%20formula.%20Would%20appreciate%20any%20assistance%20in%20editing%20the%20following%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3DIF(AND(%24B32%26gt%3B18%2C%24B32-18%26gt%3B%3DC%2431%2CScoreCard!C4-2%2CIF(AND(%24B32%26gt%3B18%2C%24B32-18%3CC%3E%3C%2FC%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%24B32%20equals%20player's%20handicap%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20C%2431%20equals%20Hole%20handicap%20(C31-K31%20for%20front%209%20and%20N31-V31%20for%20back%209)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20ScoreCard!C4%20equals%20the%20hole%20score%20(C4-T4)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-401427%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401438%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20an%20IF%20AND%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20quick%20response%2C%20Sergie.%20The%20brackets%20were%20in%20the%20wrong%20place!!!!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thank%20you%20for%20donating%20your%20time%20and%20effort%20to%20help%20out%20the%20inexperienced.%20It%20is%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401432%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20an%20IF%20AND%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401432%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F315308%22%20target%3D%22_blank%22%3E%40dmac0070%3C%2FA%3E%20%2C%20working%20with%20nested%20IF%20and%20other%20long%20formulas%20it's%20better%20to%20use%20some%20formatting.%20You%20may%20Alt%2BEnter%20in%20formula%20bar%2C%20or%20copy%2Fpaste%20formula%20to%2Ffrom%20Notepad%2C%20whatever%20-%20but%20that%20helps.%3C%2FP%3E%0A%3CP%3EIn%20you%20case%20the%20syntax%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIF(%0A%20%20%20AND(%24B32%26gt%3B18%2C%24B32-18%26gt%3B%3DC%2431)%2C%0A%20%20%20ScoreCard!C4-2%2C%0A%20%20%20IF(%0A%20%20%20%20%20%20AND(%24B32%26gt%3B18%2C%24B32-18%26lt%3BC%2431)%2C%0A%20%20%20%20%20%20ScoreCard!C4-1%2C%0A%20%20%20%20%20%20IF(%0A%20%20%20%20%20%20%20%20%20AND(%24B32%26lt%3B0%2C%24B32%2B18%26lt%3BC%2431)%2C%0A%20%20%20%20%20%20%20%20%20ScoreCard!C4%2B1%2C%0A%20%20%20%20%20%20%20%20%20IF(%0A%20%20%20%20%20%20%20%20%20%20%20%20C%2431%26lt%3B%3D%24B32%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ScoreCard!C4-1%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ScoreCard!C4)%0A%20%20%20%20%20%20)%0A%20%20%20)%0A)%3C%2FPRE%3E%0A%3CP%3EPerhaps%20it%20could%20be%20simplified%20more%20from%20content%20point%20of%20view%2C%20but%20it's%20need%20to%20play%20with%20any%20sample%20for%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E
dmac0070
New 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
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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies