Nested IF equation stops when first criteria is fulfilled but I need all criteria to be considered then cell populated


New Member
Nov 30, 2016
I'm relatively new to excel so there may be an easy fix for this or I may have made it more complicated for myself. I have 4 columns of data- A B C D and I am trying to determine how many customers are "returning". So if A>0, B=0, and C>0 then I had a customer, they left, and then returned. Another example is if A>0, B>0, C=0, and D>0 then the customer has returned. However, if A=0 and B>0 and C>0 then the customer is still a customer.

=IFERROR(IF(A2>0,"TRUE",IF(AND(A2<0,'2015 Data'!B2>0),"TRUE",IF(AND('2015 Data'!A2<0,'2015 Data'!B2>0),"FALSE",IF(AND('2015 Data'!A2>0,'2015 Data'!B2<0,C2>0),"RETURN",IF(AND('2015 Data'!A2<0,'2015 Data'!B2<0,C2>Dashboard!$B$37),"NEW",IF(AND('2015 Data'!A2>0,'2015 Data'!B2<0,'2015 Data'!C2<0,D2>0),"RETURN",IF(AND('2015 Data'!B2>0,'2015 Data'!C2<0,D2>0),"RETURN",IF(AND('2015 Data'!A2<0,'2015 Data'!B2<0,'2015 Data'!C2<0,D2>0),"TRUE","FALSE")))))))),"ERROR")

So my problem is that the equation stops at when B2>0, but I need to know this equation (or a new equation) to go further and tell me if B>0 and C=0 and D>0 that it is "RETURN". Or alternatively to tell me that if A>0 and B=0 but C>0 that it is "RETURN".

I'm not completely sure if this makes sense or if there is even a way to do this but thank you for your help!


Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi, welcome to the board.

I have to say, that formula looks horrible :).

One comment - in several places you have
< 0

Should this perhaps be
> 0
Upvote 0
what are the options for return
use an or with the and

=if(OR(AND('2015 Data'!A2>0,'2015 Data'!B2<0,C2>0), AND('2015 Data'!B2>0,'2015 Data'!C2<0,D2>0), AND('2015 Data'!A2>0,'2015 Data'!B2<0,'2015 Data'!C2<0,D2>0)), "return", etc
Last edited:
Upvote 0
Gerald's right, that formula is a bit of a mess! :)

However, it's entirely possible to fix it up, if you want to spend the time doing it. But quite often it's easier to step back and look at what you really want to do. As far as I can tell from your description, you are looking for certain patterns of numbers. Specifically, you want to find a zero between 2 non-zero values. So my first approach was to use a function to find the first non-zero (or 1 for ease of description), then find the last 1, then do a COUNTIF to see if there are any zeros between them.

1ABCDReturning?ReturningPatternsDecimal equivalent

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>

Array Formulas

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


That's the formula in E2, which must be confirmed with Control+Shift+Enter. It's quite a bit shorter than your original formula, but depending on how comfortable you are with the additional functions, and array formulas in general, it might be harder to understand.

Approach 2 yields an even shorter formula, but might be even more arcane if you're not familiar with binary counting. In column H I listed all the possible combinations of values in A:D. In column I, I picked out the ones that match your requirements and put the decimal equivalent. Then in G2 I created a formula that converts the pattern in A:D to the decimal equivalent, then check to see if that number is one of the numbers I found from column I. Also an array formula.

One final option. Not an array formula, no complicated formulas:

E2: =OR(AND(A2>0,D2>0,OR(B2=0,C2=0)),AND(A2=0,B2>0,C2=0,D2>0),AND(A2>0,B2=0,C2>0))

Using the patterns from column H, I looked for the patterns from the matching values, and came up with this version.

Lots of options, let me know if any of them work for you. These will all be a bit longer once you add your sheet references.
Upvote 0
Thank you so much! I'm editing it now, hopefully one of these methods works better!
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back