Nested IF function returning "true" when applying logic intended for numerals to text - how to fix?

[KateDu]

Hi guys,

I've got a series of values in six columns that I want my spreadsheet to look at and tell me which template I need to use - insufficient, normal or at risk.

If all values are "X", then it should (and does) return "insufficient".
If all values are outside my logic tests (i.e. <1 or >15 for the first column etc), it should return "normal".
If any of the values are within my logic tests, i.e. true, then it should return "at risk" (in the above case - the first column should return "normal" if the value falls between 1-15 but "at risk" if <1 or 16+).

The problem arises when one of the columns has an "X" rather than a value. In those cases, I need my formula to essentially return a false and continue evaluating the subsequent columns with the relevant logic tests. However, the formula is evaluating an "X" (or any other text/symbol value I put in there) as true and returning "at risk".

The formula I have is:

=IF((COUNTIF(F11,"X")=1)*AND(COUNTIF(I11:M11,"X")=5),"Insufficient",(IF(F11<1,"At risk",(IF(F11>15,"At risk",(IF(I11>149,"At risk",(IF(J11>199,"At risk",(IF(K11<40,"At risk",(IF(L11>129,"At risk",(IF(M11>29,"At risk","Normal")))))))))))))))

(Note, columns G and H are not considered in the formula as they do not influence the outcome, so they have been purposefully omitted)

I've tried doing this multiple ways and I just don't seem to be able to work it out. Please help?

 

[Alex Blakenburg]

I a might be completely off track but can you try this in S10

=IF((COUNTIF(F10,"X")=1)*(COUNTIF(I10:M10,"X")=5),
          "Insufficient",
           IF(OR(
                      (F10<1)*N(F10),
                      (F10>15)*N(F10),
                      (I10>149)*N(I10),
                      (J10>199)*N(J10),
                      (K10<40)*N(K10),
                      (L10>129)*N(L10),
                      (M10>29)*N(M10)),
                              "At risk",
                              "Normal"))

 

[KateDu]

I a might be completely off track but can you try this in S10

=IF((COUNTIF(F10,"X")=1)*(COUNTIF(I10:M10,"X")=5),
          "Insufficient",
           IF(OR(
                      (F10<1)*N(F10),
                      (F10>15)*N(F10),
                      (I10>149)*N(I10),
                      (J10>199)*N(J10),
                      (K10<40)*N(K10),
                      (L10>129)*N(L10),
                      (M10>29)*N(M10)),
                              "At risk",
                              "Normal"))

That works! Thank you so much Alex!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.
FYI: N returns a 0 if the cell is text or blank.

 

[KateDu]

Thanks for letting me know. Glad I could help.
FYI: N returns a 0 if the cell is text or blank.

So I saw after you alerted me to it. Very handy to know! This is perfect, except where a value in the cell is actually 0, but I can eyeball those - there's only be one in 190 sets of results thus far.

 

[Alex Blakenburg]

Having to eyenball it is not the best idea. Try this:
(I have only changed the ones where the value 0 matters)

=IF((COUNTIF(F10,"X")=1)*(COUNTIF(I10:M10,"X")=5),
          "Insufficient",
           IF(OR(
                      (F10<1)*ISNUMBER(F10),
                      (F10>15)*N(F10),
                      (I10>149)*N(I10),
                      (J10>199)*N(J10),
                      (K10<40)*ISNUMBER(K10),
                      (L10>129)*N(L10),
                      (M10>29)*N(M10)),
                              "At risk",
                              "Normal"))

 

[KateDu]

Having to eyenball it is not the best idea. Try this:
(I have only changed the ones where the value 0 matters)

=IF((COUNTIF(F10,"X")=1)*(COUNTIF(I10:M10,"X")=5),
          "Insufficient",
           IF(OR(
                      (F10<1)*ISNUMBER(F10),
                      (F10>15)*N(F10),
                      (I10>149)*N(I10),
                      (J10>199)*N(J10),
                      (K10<40)*ISNUMBER(K10),
                      (L10>129)*N(L10),
                      (M10>29)*N(M10)),
                              "At risk",
                              "Normal"))

That's perfect - thanks Alex!!

 

[Alex Blakenburg]

You're welcome.