Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)

[retailmessiah]

Hello Excel Problem Gurus,

First of all, let me thank you in advance. I find it exemplary that
you all can devote time to helping others who are having issues with
their work. Hopefully one day I can be at a mentor level, and help
others too.

Hope you can help! I have an issue where I don't know how to write the
formula that I need without going over on the nesting. The current
formula that I have is as follows:

=IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No
Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/
A",IF(N7="N/A",IF(O7="N/A",IF(P7="N/A","No",IF(DATEDIF($W
$2,P7,"D")<30,"Yes","No")),IF(DATEDIF($W
$2,O7,"D")<30,"Yes","No")),IF(DATEDIF($W
$2,N7,"D")<30,"Yes","No")),IF(DATEDIF($W
$2,M7,"D")<30,"Yes","No")),IF(DATEDIF($W
$2,L7,"D")<30,"Yes","No")),IF(DATEDIF($W$2,J7,"D")<30,"Yes","No"))

This formula is in a column labeled as "Expiring in 30 Days?" and
should have a result of Yes or No for each row depending on if ANY of
the dates are inside of 30 days until expiration. Let me explain. In
this formula, Cell W2 contains the current date. Cell B7 is a count
cell, and should only be in the first part. Cells J7, L7, M7, N7, O7,
and P7 all contain expiration dates. This sheet was working until I
discovered that some of the data in these cells needed to also accept
N/A as a value. With just calcing if the DATEDIF was greater than/less
than 30 days we began getting VALUE errors. The previous working
formula was as follows:

=IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No
Data",IF(V7="Yes","Expired",IF(OR(DATEDIF($W$2,J7,"D")<30,DATEDIF($W
$2,L7,"D")<30,DATEDIF($W$2,M7,"D")<30,DATEDIF($W
$2,N7,"D")<30,DATEDIF($W$2,O7,"D")<30,DATEDIF($W
$2,P7,"D")<30),"Yes","No")))


Basically I need the formula to ignore the cell if it has N/A, but not
stop the formula from checking the other cells in the row. There is
one more complicating factor. One cell, M7 we want to IGNORE
expiration dates and return a 'No' for that cell IF cell T7 has an "A"
in it.

Confused? I was too! So, all the error checking that's happening in
the above formula with the caveat that it need not flag M7 as expiring
in 30 days if cell T7="A". Ugh.

Any help would be appreciated. I've been stuck here for a few days,
and I feel like I'm overlooking something simple.

I'm thinking after reading on some forums (i.e.
http://www.computing.net/answers/office/excel-loop-formula-need-help/7159.html)
that I'd need to have an array formula(e) ? I'm not sure how to work
with several of the functions that were listed in the example, so
hopefully someone here can help me adapt this over-nested formula. I'd
really like to keep it in the cell, and not need to jump to VBA if at
all possible.

I've posted a copy of one of the sheets based on this here:
http://john.highoctane.net/Expiry.xls Hopefully being able to see the
conditional formatting that is based on this will be helpful too. I've
ran some mock numbers so you can play with the sheet and see how it
works. You'll see that it's mostly working, with the exception of the
formulae above located in column W. The specific issue is where we get
the #VALUE error.

Thanks for your assistance.
-John Phenom
(e-mail address removed) (this is a valid email, just anticipating spam once
it's out on Usenet..

 

[Daryl S]

John -

If you want to keep everything immediate (that is, not run a macro), then
you can split the contents into two other cells, and combine them into one
that is visible. If your original code was in, say, cell AA7, you could use
cells AB7 and AC7 to contain half the criteria each, and then AA7 would be
simple like this:
=IF(AB7 = "",AC7,AB7)

Where AB7 might have
=IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No
Data",IF(V7="Yes","Expired",IF(J7="N/A",IF(L7="N/A",IF(M7="N/A",IF(N7="N/A",""))

And AC7 would have the rest of the checking.

 

[Luke M]

I believe we can solve the multi-IF function by using a long OR function, and
we can bypass the N/A problem by using SUM (which ignores text). If I setup
my sheet similar to what your's looks like, this formula appears to work:

=IF(COUNTA(B7,J7,L77)<>7,"No
Data",IF(V7="Yes","Expired",IF(OR(SUM(-$W$2,J7)<=30,SUM(-$W$2,L7)<=30,AND(T7<>"A",SUM(-$W$2,M7)<=30),SUM(-$W$2,N7)<=30,SUM(-$W$2,O7)<=30,SUM(-$W$2,P7)<=30),"Yes","No")))

Since there are only 4 possible outcomes, the most IF functions you should
need is 3. Hope that helps!

 

[retailmessiah]

Hi Daryl & Luke,

Thanks for your replies. I tried to work with your formulas Daryl, but
I was struggling to get the data to check out, and was trying to avoid
extra columns, if possible. I then moved on to Luke's (obviously more
condensed/better) formula, and it didn't exactly work either.

Don't get me wrong, it mostly worked, but I was still having issues
where it was incorrectly coding lines as expiring when I imported it
back to our larger data set sheet. Then I realized that it was
reporting a Yes on expiring in 30 days when it was encountering lines
with a N/A in them. I reworked Luke's formula, and it appears to be
working. Here's what I ended up with in the production sheet:

=IF(COUNTA(B7,J7,L77)<>7,"No
Data",IF(V7="Yes","Expired",IF(OR(AND(SUM(-$W$2,J7)<=30,J7<>"N/
A"),AND(SUM(-$W$2,L7)<=30,L7<>"N/A"),AND(T7<>"A",SUM(-$W
$2,M7)<=30,M7<>"N/A"),AND(SUM(-$W$2,N7)<=30,N7<>"N/A"),AND(SUM(-$W
$2,O7)<=30,O7<>"N/A"),AND(SUM(-$W$2,P7)<=30,P7<>"N/A")),"Yes","No")))

Just nested some AND checks for the N/A and it works perfectly. I
realized after I saw Luke's formula that this was less of a formula
issue I was having, and more of a logic issue... possibly/probably not
one of my strong suits. It never occurred to me to work with the dates
as integers using normal mathematical operation functions instead of
date functions-- even though I was aware that you could, and had in
the past! Very nice!

Thank you gentlemen, and I did rate both posts on Google Groups star
rating system, I hope that's what you were looking for.

Thanks again for your assistance.
-John Phenom
(e-mail address removed)

 

[Fred Smith]

You can simplify this further because you don't need the Sum function.
L7-$W$2 is the same as SUM(-$W$2,L7). Try the following:
=IF(COUNTA(B7,J7,L77)<>7,"No
Data",IF(V7="Yes","Expired",IF(OR(AND(J7-$W$2<=30,J7<>"N/A"),AND(L7-$W$2<=30,L7<>"N/A"),AND(T7<>"A",M7-$W$2)<=30,M7<>"N/A"),AND(N7-$W$2)<=30,N7<>"N/A"),AND(O7-$W$2<=30,O7<>"N/A"),AND(P7-$W$2<=30,P7<>"N/A")),"Yes","No")))

Regards,
Fred

Hi Daryl & Luke,

Thanks for your replies. I tried to work with your formulas Daryl, but
I was struggling to get the data to check out, and was trying to avoid
extra columns, if possible. I then moved on to Luke's (obviously more
condensed/better) formula, and it didn't exactly work either.

Don't get me wrong, it mostly worked, but I was still having issues
where it was incorrectly coding lines as expiring when I imported it
back to our larger data set sheet. Then I realized that it was
reporting a Yes on expiring in 30 days when it was encountering lines
with a N/A in them. I reworked Luke's formula, and it appears to be
working. Here's what I ended up with in the production sheet:

=IF(COUNTA(B7,J7,L77)<>7,"No
Data",IF(V7="Yes","Expired",IF(OR(AND(SUM(-$W$2,J7)<=30,J7<>"N/
A"),AND(SUM(-$W$2,L7)<=30,L7<>"N/A"),AND(T7<>"A",SUM(-$W
$2,M7)<=30,M7<>"N/A"),AND(SUM(-$W$2,N7)<=30,N7<>"N/A"),AND(SUM(-$W
$2,O7)<=30,O7<>"N/A"),AND(SUM(-$W$2,P7)<=30,P7<>"N/A")),"Yes","No")))

Just nested some AND checks for the N/A and it works perfectly. I
realized after I saw Luke's formula that this was less of a formula
issue I was having, and more of a logic issue... possibly/probably not
one of my strong suits. It never occurred to me to work with the dates
as integers using normal mathematical operation functions instead of
date functions-- even though I was aware that you could, and had in
the past! Very nice!

Thank you gentlemen, and I did rate both posts on Google Groups star
rating system, I hope that's what you were looking for.

Thanks again for your assistance.
-John Phenom
(e-mail address removed)

 

[Luke M]

Fred,

You can't do a direct subtraction because the N/A (or any text value) will
cause a #VALUE error to pop-up. The error then propogates throughout the rest
of the formula, causing an overall error.

 

[Luke M]

Glad you got it to work, and thanks for the feedback!

 

[Fred Smith]

Good point. Thanks for pointing it out.

Fred

Fred,

You can't do a direct subtraction because the N/A (or any text value) will
cause a #VALUE error to pop-up. The error then propogates throughout the
rest
of the formula, causing an overall error.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*