Forum Discussion
Nested IF function with a checkbox
Hello Excel Gurus,
I have a nested IF function that is based on comparing dates and whether a checkbox is selected. It's an 18 month outlook and if the checkbox is selected it should display "O/H" otherwise a "1." It functions perfectly if there is a 1 in all 18 blocks, but if it only has a 1 in some of the blocks, the "O/H" doesn't work properly. I'm trying to put a picture in to better clarify and show how the formula is setup.
Thanks for any assistance everyone provides.
4 Replies
Hello GregPen,
The key is to decide which condition has priority.
If checking AF36 should always return O/H, regardless of whether M36 contains a date, then the checkbox test needs to be evaluated before the M36 test:
=IF($H36="","",IF($AF36,"O/H",IF($M36="",1,IF(N$6>=EDATE($M36,0),"",1))))
With this order, Excel evaluates:
H36 blank → blank
AF36 checked → O/H
M36 blank → 1
Otherwise → apply the date comparison
The original formula only checks AF36 when M36 is blank. Once M36 contains a date, Excel follows the other branch and AF36 is never evaluated.
If O/H is supposed to depend on both the checkbox and the date condition, then the checkbox needs to be incorporated into that date branch instead.
Here is what it looks like. I cut it down from 18 months to fit in a smaller picture but still show the problem.
Why didn't it change the two 1s to O/H.
The formula is:
=IF($H36="","",IF($M36="",IF($AF36,"O/H",1),IF(N$6>=EDATE($M36,0),"",1)))But if $M36 is NOT "" then it will not do the IF($AF36...) because that is part of the TRUE and it will do the IF(N$6...) statement instead. I'm not sure what to reccommend because I'm not clear on what conditionals you are checking and corresponding outputs you are expecting in each case.
You might want to consider IFS(...) or other alternatives instead of the nested IF structure.
I just don't know why it works just fine if there are 1s across all 18 months, but not when there are less.
