Nested IF statement troubles

[MrsFraser07]

Here is my nested IF statement. For some reason it has an issue when cells in column "J" are "Injector" and cells in column "M" are blank. Not sure why the IF statement isn't working on these cells. I'm telling it to look in column "N" for a date if the cell in column "J" is "Injector" so it shouldn't matter if "M" is blank. But, it obviously does. Any ideas???

=IF(J8669="No Activity","Inactive",
IF(AND(J8669="Producer",DATEDIF(M8669,$C$1,"D")<365),"Active",
IF(AND(J8669="Producer",DATEDIF(M8669,$C$1,"D")>=365),"Inactive",
IF(AND(J8669="Injector",DATEDIF(N8669,$C$1,"D")<365),"Active",
IF(AND(J8669="Injector",DATEDIF(N8669,$C$1,"D")>=365),"Inactive","??")))))


Thanks for your help!

 

[DanteAmor]

I tried your formula and it works for me, the cells in M ​​are blank and I pull the data from N

What data do you have C1 and in N8669 and what result do you get?

It just does not work if in cell J8669 it does not say exactly "Injector" (check if you have blank spaces)

 

[MrsFraser07]

I tried your formula and it works for me, the cells in M ​​are blank and I pull the data from N

What data do you have C1 and in N8669 and what result do you get?

It just does not work if in cell J8669 it does not say exactly "Injector" (check if you have blank spaces)

The cells in column "J" are formulas as well, so they are consistent, no hidden spaces. In cell C1 I have the date 2019-01-31.

It is driving me crazy!!! Strange that it works for you. I am using Windows 10 & Excel 2016. I wouldn't think the version of excel would be the issue.

 

[Fluff]

What happens if col M is blank as opposed to when it's not blank?

 

[joeu2004]

Change the formula as follows:

=IF(J8669="No Activity", "Inactive",
IF(J8669="Producer",IF($C$1-M8669<365,"Active","Inactive"),
IF(J8669="Injector",IF($C$1-N8669<365,"Active","Inactive"), "??")))

You neglect to say in what way the statement "isn't working". I wonder if you mean: it returns #VALUE .

I suspect that when M8669 "is blank", you mean: it appears blank, but its actual value is the null string (or some other string that appears blank).

In that case, you are still looking at M8669 when J8669="Injector" because the AND function evaluates all of its arguments.

The restructured nested IF expression avoids looking at M8669 unless J8669="Producer".

BTW, the IF expression will still throw an error (#VALUE ) if M8669 is the null string when J8669="Producer". If that is a possibility, a quick-and-dirty remedy is to write N(M8669) instead. Similarly with N8669.

Aside.... Unrelated to your problem, there is (usually) no good reason to use DATEDIF(...,"d"). Simply subtract the dates, as I do above.

Also, again unrelated to your problem, there is no reason to test both "<365" and ">=365", at least when comparing to an integer. If "<365" is false, ">=365" must be true.

 

[MrsFraser07]

Change the formula as follows:

=IF(J8669="No Activity", "Inactive",
IF(J8669="Producer",IF($C$1-M8669<365,"Active","Inactive"),
IF(J8669="Injector",IF($C$1-N8669<365,"Active","Inactive"), "??")))

You neglect to say in what way the statement "isn't working". I wonder if you mean: it returns [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] .

I suspect that when M8669 "is blank", you mean: it appears blank, but its actual value is the null string (or some other string that appears blank).

In that case, you are still looking at M8669 when J8669="Injector" because the AND function evaluates all of its arguments.

The restructured nested IF expression avoids looking at M8669 unless J8669="Producer".

BTW, the IF expression will still throw an error ([URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] ) if M8669 is the null string when J8669="Producer". If that is a possibility, a quick-and-dirty remedy is to write N(M8669) instead. Similarly with N8669.

Aside.... Unrelated to your problem, there is (usually) no good reason to use DATEDIF(...,"d"). Simply subtract the dates, as I do above.

Also, again unrelated to your problem, there is no reason to test both "<365" and ">=365", at least when comparing to an integer. If "<365" is false, ">=365" must be true.

Thanks!!! This totally worked! I did mean #VALUE by "not working", next time I'll be more specific. Also, Yes, I knew that M8669 wasn't actually blank, as that is a VLOOKUP formula too. I used DATEDIF sometimes to figure out the number of days between two dates, but your tip of simply just subtracting it is much better, less complicated! I guess I overcomplicated this one. Really appreciate your help, this solved my problem!