Shortening a massive IF statement

[mattyhousecat]

Hello Friends, im actually a Wintel Server Admin / DBA who just got thrown a excel spreadsheet to fix, im not the strongest with formalue, i need to shorten the following code because its huge.

thanks in advance if someone can do it <3

=IF(ISBLANK([@[Assigned To]]),"Select staff",IF(AND(AX2=1,AY2<>"No"),"Yes",IF(AND(AX2=2,AY2<>"No",AZ2<>"No"),"Yes",IF(AND(AX2=3,AY2<>"No",AZ2<>"No",BA2<>"No"),"Yes",IF(AND(AX2=4,AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No"),"Yes",IF(AND(AX2=5,AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No"),"Yes",IF(AND(AX2=5,AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No")"Yes",IF(AND(AX2=5,AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No",BE<>"No")"Yes",IF(AND(AX2=5,AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No",BE<>"No",BF<>"No")"Yes",IF(AND(AX2=5,AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No",BE<>"No",BF<>"No",BG<>"No")"Yes",IF(AND(AX2=5,AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No",BE<>"No",BF<>"No",BG<>"No",BH<>"No")"Yes","No")))))))))))

 

[StephenCrump]

Welcome to the Forum!

Ahh, the joys of fixing someone else's spreadsheet. Good luck with that.

For starters, your formula as posted has incorrect syntax ... various references to BE, BF etc should presumably be BE2, BF2 etc.

Ignoring for the moment the table reference: [@[Assigned To]], it appears that the intent of the formula is:

- There is a numeric value 1,2,3,4 .. etc in AX2 (let's call this N)
- The ten columns AY2:BH2 have either "No" or some other value
- The formula should return "No" if there is "No" anywhere in the first N columns, otherwise "Yes"

So for example, if AX2 is 3, the formula will return "No" if there is "No" anywhere in the 3 columns "AY2:BA2", otherwise "Yes".

If so, we can simplify the bulk of the formula to

<ax2)),"no","yes")
=IF(SUMPRODUCT((AY2:BH2="No")*(COLUMN(AY2:BH2)-COLUMN(AY2) < AX2)),"No","Yes")
<ax2)),"no","yes")

This is not exactly how the formula is written, but it looks like the red 5's highlighted below should be 6,7,8,9,10 respectively. (Otherwise those bits of the formula would be redundant). My guess is that whoever set up the spreadsheet was copying and pasting within the formula and overlooked updating these numbers.

=IF(ISBLANK([@[Assigned To]]),"Select staff",IF(AND(AX2=1,AY2<>"No"),"Yes",IF(AND(AX2=2,AY2<>"No",AZ2<>"No"),"Yes",IF(AND(AX2=3,AY2<>"No",AZ2<>
"No",BA2<>"No"),"Yes",IF(AND(AX2=4,AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No"),"Yes",IF(AND(AX2=5,AY2<>"No
",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No"),"Yes",IF(AND(AX2=[COLOR=#ff0000][B]5[/B][/COLOR],AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No"
,BC2<>"No",BD2<>"No")"Yes",IF(AND(AX2=[COLOR=#ff0000][B]5[/B][/COLOR],AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No",
BE<>"No")"Yes",IF(AND(AX2=[COLOR=#ff0000][B]5[/B][/COLOR],AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No",BE<>"No",BF<
>"No")"Yes",IF(AND(AX2=[B][COLOR=#ff0000]5[/COLOR][/B],AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No",BE<>"No",BF<>"N
o",BG<>"No")"Yes",IF(AND(AX2=[COLOR=#ff0000][B]5[/B][/COLOR],AY2<>"No",AZ2<>"No",BA2<>"No",BB2<>"No",BC2<>"No",BD2<>"No",BE<>"No",B
F<>"No",BG<>"No",BH<>"No")"Yes","No")))))))))))

I am not sure how the table reference fits in. Where is this located in the spreadsheet, and where is the formula that you posted located?</ax2)),"no","yes")
</ax2)),"no","yes")

 

[mattyhousecat]

ive acutally fixed this, there was just 3 comma's missing from the formulae. BD2<>"No")"Yes"

should be BD2<>"No"),"Yes"

 

[mattyhousecat]

thanks for looking into this homie, lots of love

matty

 

[StephenCrump]

ive acutally fixed this, there was just 3 comma's missing from the formulae. BD2<>"No")"Yes"

should be BD2<>"No"),"Yes"

You may have fixed the syntax, i.e. so that the formula returns values: "Select Staff", "Yes" or "No".

But you won't have fixed the logic, and I doubt the formula is doing what was intended, i.e. this formula and any dependent formula may be producing incorrect results.

Do you have actually any values in cells BD2:BH2? Although the formula appears to reference these cells, those parts of the formula are redundant, i.e. it doesn't matter what's in those cells - the formula effectively ignores them.

 

[thisoldman]

If StephenCrump is correct about the missing 6, 7, 8, etc., this formula, I think should work:

=IF(ISBLANK([@[Assigned To]]),
  "Select Staff",
  IF(COUNTIF(AY2:INDEX(AY2:BH2, ,AX2),"No")<>AX2,"Yes","No"))

AY2:INDEX(AY2:BH2, ,AX2) is a dynamic range. It expands and contracts over the the columns of AY2:BH2. The value of AX2 determines the number of columns included in the range.

When the number of "No"s in that dynamic range does not equal the value of AX2, the IF statement returns "Yes"; otherwise, the IF returns "No".

 

[thisoldman]

This is easier for me to understand: I get confused counting things that aren't "No".

=IF(ISBLANK([@[Assigned To]]),
  "Select Staff",
  IF(COUNTIF(AY2:INDEX(AY2:BH2, ,AX2),"No")=0,"Yes","No"))

If there is any "No" in the dynamic range, the IF returns "No"; otherwise, "Yes".