More than 7 Nested IFs

%3CLINGO-SUB%20id%3D%22lingo-sub-644448%22%20slang%3D%22en-US%22%3EMore%20than%207%20Nested%20IFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644448%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20list%20in%20SharePoint%20(Office%20365)%20and%20trying%20to%20create%20a%20calculated%20Returning%20a%20%22Yes%22%20of%20%22No%22.%26nbsp%3B%20Below%20is%20the%20formula%20I'm%20working%20with.%26nbsp%3B%20Problem%20is%20that%20I%20have%2014%20conditions%2C%20which%20is%20more%20than%20the%20allowable%207.%26nbsp%3B%26nbsp%3BAny%20ideas%20n%20how%20I%20can%20overcome%20this%20limitation%3F%3C%2FP%3E%3CP%3E%3D%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Stand-Alone%20Construction%22%2C%5BContract%20Value%5D%26gt%3B%3D700000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Stand-Alone%20Construction%22%2C%5BContract%20Value%5D%26lt%3B700000)%2C%22No%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Construction%20IDIQ%22%2C%5BContract%20Value%5D%26gt%3B%3D700000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Construction%20IDIQ%22%2C%5BContract%20Value%5D%26lt%3B700000)%2C%22No%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Construction%20IDIQ%20Task%20Order%22%2C%5BContract%20Value%5D%26gt%3B%3D700000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Construction%20IDIQ%20Task%20Order%22%2C%5BContract%20Value%5D%26lt%3B700000)%2C%22No%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Stand-Alone%20A%2FE%22%2C%5BContract%20Value%5D%26gt%3B%3D35000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Stand-Alone%20A%2FE%22%2C%5BContract%20Value%5D%26lt%3B35000)%2C%22No%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22A%2FE%20IDIQ%22%2C%5BContract%20Value%5D%26gt%3B%3D35000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22A%2FE%20IDIQ%22%2C%5BContract%20Value%5D%26lt%3B35000)%2C%22No%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22A%2FE%20IDIQ%20Task%20Order%22%2C%5BContract%20Value%5D%26gt%3B%3D35000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22A%2FE%20IDIQ%20Task%20Order%22%2C%5BContract%20Value%5D%26lt%3B35000)%2C%22No%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Consulting%20(Services)%22%2C%5BContract%20Value%5D%26gt%3B%3D250000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BContract%20Type%5D%3D%22Consulting%20(Services)%22%2C%5BContract%20Value%5D%26lt%3B250000)%2C%22No%22%2C%22N%2FA%22))))))))))))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644972%22%20slang%3D%22en-US%22%3ERe%3A%20More%20than%207%20Nested%20IFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644972%22%20slang%3D%22en-US%22%3E%3CP%3ENo%20need%20to%20reply%20-%20I%20figured%20it%20out%3A%3C%2FP%3E%3CP%3E%3D%3CBR%20%2F%3EIF(AND(%5BCONTRACT%20TYPE%5D%3D%22A%2FE%20IDIQ%22%2C%5BCURRENT%20VALUE%5D%26gt%3B%3D35000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BCONTRACT%20TYPE%5D%3D%22A%2FE%20IDIQ%20Task%20Order%22%2C%5BCURRENT%20VALUE%5D%26gt%3B%3D35000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BCONTRACT%20TYPE%5D%3D%22Stand-Alone%20A%2FE%22%2C%5BCURRENT%20VALUE%5D%26gt%3B%3D35000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BCONTRACT%20TYPE%5D%3D%22Construction%20IDIQ%22%2C%5BCURRENT%20VALUE%5D%26gt%3B%3D700000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BCONTRACT%20TYPE%5D%3D%22Construction%20IDIQ%20Task%20Order%22%2C%5BCURRENT%20VALUE%5D%26gt%3B%3D700000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BCONTRACT%20TYPE%5D%3D%22Stand-Alone%20Construction%22%2C%5BCURRENT%20VALUE%5D%26gt%3B%3D700000)%2C%22Yes%22%2C%3CBR%20%2F%3EIF(AND(%5BCONTRACT%20TYPE%5D%3D%22Consulting%20(Services)%22%2C%5BCURRENT%20VALUE%5D%26gt%3B%3D250000)%2C%22Yes%22%2C%3CBR%20%2F%3E%22No%22)))))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644978%22%20slang%3D%22en-US%22%3ERe%3A%20More%20than%207%20Nested%20IFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644978%22%20slang%3D%22en-US%22%3Eif%20you%20routinely%20do%20complex%20things%20like%20that%2C%20you%20will%20want%20to%20take%20a%20look%20at%20PowerApps%20which%20contains%20a%20much%20more%20powerful%20language%20for%20complex%20formulas%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644985%22%20slang%3D%22en-US%22%3ERE%3A%20More%20than%207%20Nested%20IFs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644985%22%20slang%3D%22en-US%22%3EI%20will%20take%20a%20look.%20I%20am%20new%20to%20this%20so%20thanks%20for%20the%20tip%20Dean!%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am working on a list in SharePoint (Office 365) and trying to create a calculated Returning a "Yes" of "No".  Below is the formula I'm working with.  Problem is that I have 14 conditions, which is more than the allowable 7.  Any ideas n how I can overcome this limitation?

=
IF(AND([Contract Type]="Stand-Alone Construction",[Contract Value]>=700000),"Yes",
IF(AND([Contract Type]="Stand-Alone Construction",[Contract Value]<700000),"No",
IF(AND([Contract Type]="Construction IDIQ",[Contract Value]>=700000),"Yes",
IF(AND([Contract Type]="Construction IDIQ",[Contract Value]<700000),"No",
IF(AND([Contract Type]="Construction IDIQ Task Order",[Contract Value]>=700000),"Yes",
IF(AND([Contract Type]="Construction IDIQ Task Order",[Contract Value]<700000),"No",
IF(AND([Contract Type]="Stand-Alone A/E",[Contract Value]>=35000),"Yes",
IF(AND([Contract Type]="Stand-Alone A/E",[Contract Value]<35000),"No",
IF(AND([Contract Type]="A/E IDIQ",[Contract Value]>=35000),"Yes",
IF(AND([Contract Type]="A/E IDIQ",[Contract Value]<35000),"No",
IF(AND([Contract Type]="A/E IDIQ Task Order",[Contract Value]>=35000),"Yes",
IF(AND([Contract Type]="A/E IDIQ Task Order",[Contract Value]<35000),"No",
IF(AND([Contract Type]="Consulting (Services)",[Contract Value]>=250000),"Yes",
IF(AND([Contract Type]="Consulting (Services)",[Contract Value]<250000),"No","N/A"))))))))))))))

3 Replies
Highlighted

No need to reply - I figured it out:

=
IF(AND([CONTRACT TYPE]="A/E IDIQ",[CURRENT VALUE]>=35000),"Yes",
IF(AND([CONTRACT TYPE]="A/E IDIQ Task Order",[CURRENT VALUE]>=35000),"Yes",
IF(AND([CONTRACT TYPE]="Stand-Alone A/E",[CURRENT VALUE]>=35000),"Yes",
IF(AND([CONTRACT TYPE]="Construction IDIQ",[CURRENT VALUE]>=700000),"Yes",
IF(AND([CONTRACT TYPE]="Construction IDIQ Task Order",[CURRENT VALUE]>=700000),"Yes",
IF(AND([CONTRACT TYPE]="Stand-Alone Construction",[CURRENT VALUE]>=700000),"Yes",
IF(AND([CONTRACT TYPE]="Consulting (Services)",[CURRENT VALUE]>=250000),"Yes",
"No")))))))

Highlighted
if you routinely do complex things like that, you will want to take a look at PowerApps which contains a much more powerful language for complex formulas
Highlighted
I will take a look. I am new to this so thanks for the tip Dean!