Help with nested IF AND function

[Hayche]

Hi,

I've been working on the formula below for a long time and could really do with some help. Basically the formula is set to divide column H5 (80%) with column J5 (Duration) as long as the month columns in orange fall within the From (K5) and To (L5) date range. This all works fine, but then when it comes to the second nested IF function, the formula is supposed to divide the 20% column (I5) with the duration (J5) if the date in orange (AG in this case) matches the EPA column (M5). This is the part that doesn't work. 

=IF(AND(AG4>=$K$5,AG4<=$L$5),$H$5/$J$5,IF(AG4=$M$5,$I$5/$J$5," "))

Any ideas on how to fix this formula? I've added a screenshot too. Any help would be greatly appreciated.

Thanks,

Hayche 

[#2]

@Hayche 

What do you see in the formula bar when you select cell M5? And what do you see there when you select AG4?

[#3]

@Hayche 

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

1. 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.
2. 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)

[#4]

@Hayche 

It is difficult for us to debug problems based on images alone.  "If a picture is worth a thousand words, an Excel file is worth a thousand pictures".  :wink:

Attach a (redacted) Excel file that demonstrates the problem.  Click "browse" at the bottom of the reply window.  If you cannot attach a file, upload the file to a file-sharing website and post the download URL.

Obviously, M5 does not equal AG4, despite appearances.

If both M5 and AG4 are text,  they might differ by "invisible" characters.

Assuming that both M5 and AG4 are numeric, they might be differ dates, even if they appear to be the same month and year due to formatting.  For example, M5 might be 9/1/2023 and AG4 might be 9/30/2023.

And of course, M5 might be text and AG4 might be a numeric date formatted as mmm-yy, or vice versa.

Use ISTEXT to distinguish text from numeric dates.  Looks can be deceiving; and the format of the cell does not matter.

-----

PS....  It is better to return the null string ("") instead of a string of one or more spaces (" ").  With the null string, conditions like IF(A1="", ...) work as intended whether A1 is empty (no value) or the null string.

[#5]

Hi all,

Thanks for the fast responses and apologies for not posting the excel sheet to begin with.
Here's the link to the file on One Drive: https://1drv.ms/x/s!AhVNGlcJB_F8gYxsT5XBkkCacv0XeQ?e=5alCia

[#6]

@Hayche 

Almost exactly as I had guessed(!):  M5 is 9/30/2023, whereas AG4 is 9/1/2023.

To see that, temporarily format both cells as Custom m/d/yyyy.

IMHO, the simplest and best fix is to use dates consistently:  always the 1st of each month (best, IMHO) or always the end of each month (which varies from 28 to 31 :sigh:).

PS....  On second thought, I want to emphasize just how bad it is to use the end of the month.  Then "the end of Feb 2020" (2/29/2020) is not equal to "the end of Feb 2021" (2/28/2021) in its simplest form.  Of course, there are ways to make the comparison work, if necessary.

If you "cannot" do that, we can offer other (much more complicated) suggestions to make the comparisons work as intended.

[#7]

@Hayche 

As @Joe User has already pointed out, your dates are not what you think they are. I'd agree with him that you need to fix those first. Personally I'd recommend using the "Short Date" format. That way you'll be getting better results.

When you apply that format to your column L you'll see that because you entered your dates as shown here:

they actually are registered as follows:

This is because Excel thinks it's being helpful (and it is, if you know that's what its doing) by adding a year to things when the user enters only the month & day, but it always assumes you, the user, are wanting the current year. So your Jun-23 became 6/23/22 in the underlying number, and so forth: what you intended as future years ended up as dates in 2022, some of which preceded the start date!!

So let me ask you to do three things:

1. Fix the dates so they mean what you want them to mean.
2. Re-post a file, and do so in a manner that allows us to edit, not just view. I had to copy the whole thing to my own computer in order to see what the underlying issues were. Post a copy of your original so whatever changes we make don't affect your original. 
3. By the way, if those are real names of real people, remove them too.

[#8 · Accepted solution]

@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.

[#9]

Joe, you're absolutely bang on! I never realized that column M was different to the orange months on the right because of the formatting - was too busy thinking I had made a mistake on the forumla itself. I took your advice and incorportated EOMONTH and the formula now works like a charm.

Thanks for the help, Joe and Mathetes too. I couldn't have done it without you (or it would have taken a whole lot longer anyway!)

Hayche