Forum Discussion
Help with nested IF AND function
Hayche .... I wrote: ``I want to emphasize just how bad it is to use the end of the month``
On second thought, looking at the formula in column L, it is clear that your intent is for columns L and M to contain end-of-month dates, if we can assume that column K always contains first-of-month dates, as it does.
So, the simplest fix might be:
=IF(AND(S4>=$K$5, S4<=$L$5), $H$5/$J$5, IF(EOMONTH(S4,0)=$M$5, $I$5/$J$5, ""))
Alternatively, you could have end-of-month dates in R4:BL4.
But in that case, the formulas in S4:BL4 should be changed from =EDATE(R4,1) to =EOMONTH(R4,1).
(Compare the difference in S4 if R4 is 6/30/2022.)
However, if you change R4:BL4 to end-of-month dates, I don't know how that might affect other dependent formulas in your original Excel file, assuming the attach Excel file is an excerpt.
Your formula is:
=IF(AND(AG4>=$K$5,AG4<=$L$5),$H$5/$J$5,IF(AG4=$M$5,$I$5/$J$5," "))
Could you post the original spreadsheet to the OneDrive? or Google Sheets? I'm not inclined to take the time to recreate, so I can't test what I'm saying below.
So for that reason, I can't be sure of this, but
- I think your absolute and relative references are reversed from what they should be. Row 4 contains the headings, so I would think that you'd always be comparing AG$4 with K5, L5, M5 rather than the other way around.
- More to the point, though, you might find that using IFS (rather than a nested IF) would be cleaner,
=IFS(AND(AG$4>=K5,AG$4<=L5),H5/J5,AG$4=M5,I5/J5)
If neither of those helps, please come back with the actual spreadsheet so we can work with more than an image. (Or post to a shared drive and grant access)