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

#### VRose

##### New Member
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!

<tbody>
</tbody>

### 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
or
> 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:
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.

ABCDEFGHI
1ABCDReturning?ReturningPatternsDecimal equivalent
21011TRUETRUE0000
31010TRUETRUE0001
41101TRUETRUE0010
50110FALSEFALSE0011
60111FALSEFALSE0100
70010FALSEFALSE01015
8FALSEFALSE0110
9FALSEFALSE0111
10FALSEFALSE1000
11FALSEFALSE10019
12101010
13101111
141100
15110113
161110
171111

</tbody>
Sheet17

Array Formulas
CellFormula
E2{=IFERROR(COUNTIF(OFFSET(A2,0,MIN(IF(A2:D2>0,COLUMN(A2:D2)))-1):OFFSET(A2,0,MAX(IF(A2:D2>0,COLUMN(A2:D2)))-1),0)>0,FALSE)}
G2{=ISNUMBER(MATCH(SUM((A2:D2>0)*(2^(4-COLUMN(A2:D2)))),{5,9,10,11,13},0))}

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

<tbody>
</tbody>

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.

Thank you so much! I'm editing it now, hopefully one of these methods works better!

Replies
7
Views
225
Replies
0
Views
149
Replies
18
Views
847
Replies
0
Views
171
Replies
23
Views
755

1,196,248
Messages
6,014,223
Members
441,808
Latest member
xplainer

### 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 MrExcel.com.

### Which adblocker are you using?

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

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
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