Forum Discussion
Nested IF function with a checkbox
Hello GregPen,
That clarification makes the intended logic clearer.
The checkbox should only change a cell that would otherwise contain 1. It should not override cells that the date logic would leave blank.
Try:
=IF($H36="","",IF($M36="",IF($AF36,"O/H",1),IF(N$6>=EDATE($M36,0),"",IF($AF36,"O/H",1))))
The key is that AF36 is checked only after the formula determines that the result for that month should be 1.
The logic is:
H36 is blank → blank
M36 is blank → 1, or O/H if AF36 is TRUE
N6 is on or after the PDD date → blank
Otherwise → 1, or O/H if AF36 is TRUE
This keeps the existing date cutoff while replacing only the 1s with O/H when the checkbox is selected.
It also keeps the AF36 test out of the EDATE calculation when M36 is blank, avoiding the #VALUE! problem.
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.
This formula works better, but it changes all the cells to O/H in the rom instead of just the cells with a 1. I think an AND function needs to be added as a final check in the formula but when I tried to come up with one I get a "too many arguments" error.