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.
Thanks for the explanation. I figured it was an ordering problem but I get lost in the multiple IFs. This did partially fix the problem. It brings up O/H now but across the entire row and not just where the 1s populate. Would there be a better formula for this or are nested IFs the way to go?
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.
Thank you for the assistance. It looks like it's working as I need now. I'm going to add this to a document I put together to help me better understand multiple-IF nesting.