Combining IF(OR(ISBLANK...

[BBS]

Hi

I am having problems with the formula

=IF(OR(ISBLANK(F16:N16)),"Input Req'd",IF(E16=0,"",IF(ISNUMBER(MATCH("overdue",O16,0)),"Overdue",IF(ISNUMBER(MATCH("Today",O16,0)),"Today",IF(ISNUMBER(MATCH("Tomorrow",O16,0)),"Tomorrow","ok")))))

The results I'm looking for are:

If any cells in the range F16:N16 are blank, return "Input Req'd"
If all cells contain data, return "Overdue" / "Today" / "Tomorrow" if matched, or "ok" if not.

F16:N16 are blank cells, with no formulae.

When I use the edit formula button to check, it shows the expected result, but When there are blank cells, the cell return is "ok"

Thanks for your time

Jon

[NBVC]

Use Countblank(). This counts number of blanks in the range.

Also, not sure why you have all those ISNUMBER(MATCH(... functions...not needed here.

=IF(COUNTBLANK(F16:N16)>0,"Input Req'd",IF(E16=0,"",IF(O16="overdue","Overdue",IF(O16="Today","Today",IF(O16="Tomorrow","Tomorrow","ok")))))

[BBS]

Perfect, thanks for your help!

[NBVC]

You're welcome....

Here's an even better/shorter formula....

=IF(COUNTBLANK(F16:N16)>0,"Input Req'd",IF(E16=0,"",IF(OR(O16={"Overdue","Today","Tomorrow"}),O16,"ok")))

[BBS]

You're welcome....

Here's an even better/shorter formula....

=IF(COUNTBLANK(F16:N16)>0,"Input Req'd",IF(E16=0,"",IF(OR(O16={"Overdue","Today","Tomorrow"}),O16,"ok")))

Thanks again, works a treat.

Would you mind explaining how this works, as I have other formulae which I may be able to simplify using a similar solution?

[NBVC]

COUNTBLANK(F16:N16)>0...this part counts blanks within the specified range and if there is more than 0, then the result is TRUE and so "Input Req'd" is returned.

If there are no blanks, then the second part of the formula kicks in... IF(E16=0,"",IF(OR(O16={"Overdue","Today","Tomorrow"}),O16,"ok"))

This part first checks what is in E16 and if there is a 0, it returns a blank and ends. If it not a 0, then the formula checks to see if O16 contains either of those 3 words. If either word is found, it simply returns what is in O16 (which would be the respective word found). If it doesn't find either word, then "ok" is returned.

This part: OR(O16={"Overdue","Today","Tomorrow"}) is just a simplified way of writing the more familiar.... OR(O16="Overdue",O16="Today",O16="Tomorrow") ... no need to repeat the O16 reference.

Hope that helps.