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

[VRose]

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>

 

[Gerald Higgins]

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
?

 

[etaf]

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

 

[Eric W]

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.

	A	B	C	D	E	F	G	H	I
1	A	B	C	D	Returning?		Returning	Patterns	Decimal equivalent
2	1	0	1	1	TRUE		TRUE	0000
3	1	0	1	0	TRUE		TRUE	0001
4	1	1	0	1	TRUE		TRUE	0010
5	0	1	1	0	FALSE		FALSE	0011
6	0	1	1	1	FALSE		FALSE	0100
7	0	0	1	0	FALSE		FALSE	0101	5
8					FALSE		FALSE	0110
9					FALSE		FALSE	0111
10					FALSE		FALSE	1000
11					FALSE		FALSE	1001	9
12								1010	10
13								1011	11
14								1100
15								1101	13
16								1110
17								1111

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

Sheet17​

Array Formulas Cell Formula 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))} <thead> </thead><tbody> </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.

 

[VRose]

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