More than 7 Nested IFs

Copper 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

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")))))))

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
I will take a look. I am new to this so thanks for the tip Dean!