Forum Discussion

Tin Contributor

Aug 20, 2026

Solved

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" o...

excel

Olufemi7
Aug 21, 2026
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.

GregPen

Tin Contributor

Aug 20, 2026

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?

Olufemi7

Steel Contributor

Aug 21, 2026

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.

GregPen
Tin Contributor
Aug 23, 2026
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.