How can I create an alternative to 28 levels of nested IF's?

[JeffGrant]

Hi All,

recently my model has started to give me Circular Errors that I have not had before. (Maybe because I did and Office 365 update). I read somewhere that if there are too many IF's that this could cause the problem. And indeed elsewhere in the model, I changed a multi level IF statement and that circular error was fixed. So I guess there is a truth to the statement.

However, in this case, I am not sure what to do. I have an equation which is 28 levels deep with a nested IF. All of the examples and videos I have seen using alternatives to Nested If's are very simplistic and don't really give me any help. The aim of this formula is to identify patterns in the numbers, such that:

If:
A4 = B4, B4 = C4, C4 = D4, the pattern is "same same same"

Or If
A4 < B4, B4 > C4, C4 < D4, the pattern is "up down up" and so on.

Currently, the nested IF's pulls out 26 different patterns.

I am not sure how to construct an IFS, CHOOSE or VLOOKUP equation that can replace this nested IF rats nest to fix the circular reference.

Can somebody please give me a big push into the direction I should be heading.

Thanks

Book2
	A	B	C	D	E
1
2	Numerical Equivalent from All Classes				Decider Equation
3	3	2	1	This Race
4	216	240	230	240	up down up
5	210	210	210	208	same same down
6	210	212	210	210	up down same
7	294	210	210	210	down same same
8	210	210	230	218	same up down
9	218	230	218	218	up down same
10	257	212	218	218	down up same
11	210	210	210	208	same same down
12	210	210	210	208	same same down
13	210	210	210	208	same same down
14	272	258	257	258	down down up
15	240	240	216		Do Not Bet
16	240	254	254	254	up same same
17	232	220	224	232	down up up
18	224	216	216	196	down same down
19	212	204	216	230	down up up
20	266	230	232	198	down up down
Sheet1

Cell Formulas
Range	Formula
E4:E13	E4	= IF(OR(A4="",B4="",C4="",D4=""),"Do Not Bet", IF(AND(A4=B4,A4=C4,A4=D4),"same same same",IF(AND(A4=B4,A4=C4,C4>D4),"same same down", IF(AND(A4>B4,B4<C4,D4<C4),"down up down",IF(AND(A4<B4,B4=C4,D4<C4),"up same down", IF(AND(A4>B4,B4=C4,B4=D4),"down same same",IF(AND(A4=B4,C4<B4,D4<C4),"same down down", IF(AND(A4=B4,B4<C4,D4=C4),"same up same",IF(AND(A4=B4,A4=C4,D4>C4),"same same up", IF(AND(A4<B4,B4=C4,C4=D4),"up same same",IF(AND(A4=B4,B4<C4,C4>D4),"same up down", IF(AND(A4>B4,B4>C4,C4=D4),"down down same",IF(AND(A4=B4,B4>C4,C4=D4),"same down same", IF(AND(A4<B4,B4<C4,C4>D4),"up up down",IF(AND(A4<B4,B4=C4,C4,D4),"up same up", IF(AND(A4>B4,B4<C4,C4=D4),"down up same",IF(AND(A4>B4,B4>C4,C4>D4),"down down down", IF(AND(A4>B4,B4<C4,C4<D4),"down up up",IF(AND(A4=B4,B4<C4,C4<D4),"same up up", IF(AND(A4=B4,B4>C4,C4<D4),"same down up",IF(AND(A4<B4,B4>C4,C4>D4),"up down down", IF(AND(A4>B4,B4=C4,C4<D4),"down same up",IF(AND(A4<B4,B4>C4,C4=D4),"up down same", IF(AND(A4<B4,B4<C4,C4<D4),"up up up",IF(AND(A4>B4,B4>C4,C4<D4),"down down up", IF(AND(A4<B4,B4>C4,C4<D4),"up down up",IF(AND(A4<B4,B4<C5,C4=D4),"up up same", IF(AND(A4>B4,B4=C4,C4>D4),"down same down","Do Not Bet"))))))))))))))))))))))))))))
E14	E14	= IF(OR(A14="",B14="",C14="",D14=""),"Do Not Bet", IF(AND(A14=B14,A14=C14,A14=D14),"same same same",IF(AND(A14=B14,A14=C14,C14>D14),"same same down", IF(AND(A14>B14,B14<C14,D14<C14),"down up down",IF(AND(A14<B14,B14=C14,D14<C14),"up same down", IF(AND(A14>B14,B14=C14,B14=D14),"down same same",IF(AND(A14=B14,C14<B14,D14<C14),"same down down", IF(AND(A14=B14,B14<C14,D14=C14),"same up same",IF(AND(A14=B14,A14=C14,D14>C14),"same same up", IF(AND(A14<B14,B14=C14,C14=D14),"up same same",IF(AND(A14=B14,B14<C14,C14>D14),"same up down", IF(AND(A14>B14,B14>C14,C14=D14),"down down same",IF(AND(A14=B14,B14>C14,C14=D14),"same down same", IF(AND(A14<B14,B14<C14,C14>D14),"up up down",IF(AND(A14<B14,B14=C14,C14,D14),"up same up", IF(AND(A14>B14,B14<C14,C14=D14),"down up same",IF(AND(A14>B14,B14>C14,C14>D14),"down down down", IF(AND(A14>B14,B14<C14,C14<D14),"down up up",IF(AND(A14=B14,B14<C14,C14<D14),"same up up", IF(AND(A14=B14,B14>C14,C14<D14),"same down up",IF(AND(A14<B14,B14>C14,C14>D14),"up down down", IF(AND(A14>B14,B14=C14,C14<D14),"down same up",IF(AND(A14<B14,B14>C14,C14=D14),"up down same", IF(AND(A14<B14,B14<C14,C14<D14),"up up up",IF(AND(A14>B14,B14>C14,C14<D14),"down down up", IF(AND(A14<B14,B14>C14,C14<D14),"up down up",IF(AND(A14<B14,B14<C14,C14=D14),"up up same", IF(AND(A14>B14,B14=C14,C14>D14),"down same down","Do Not Bet"))))))))))))))))))))))))))))
E15:E20	E15	=IF(OR(A15="",B15="",C15="",D15=""),"Do Not Bet",IF(AND(A15=B15,A15=C15,A15=D15),"same same same",IF(AND(A15=B15,A15=C15,C15>D15),"same same down",IF(AND(A15>B15,B15<C15,D15<C15),"down up down",IF(AND(A15<B15,B15=C15,D15<C15),"up same down",IF(AND(A15>B15,B15=C15,B15=D15),"down same same",IF(AND(A15=B15,C15<B15,D15<C15),"same down down",IF(AND(A15=B15,B15<C15,D15=C15),"same up same",IF(AND(A15=B15,A15=C15,D15>C15),"same same up",IF(AND(A15<B15,B15=C15,C15=D15),"up same same",IF(AND(A15=B15,B15<C15,C15>D15),"same up down",IF(AND(A15>B15,B15>C15,C15=D15),"down down same",IF(AND(A15=B15,B15>C15,C15=D15),"same down same",IF(AND(A15<B15,B15<C15,C15>D15),"up up down",IF(AND(A15<B15,B15=C15,C15,D15),"up same up",IF(AND(A15>B15,B15<C15,C15=D15),"down up same",IF(AND(A15>B15,B15>C15,C15>D15),"down down down",IF(AND(A15>B15,B15<C15,C15<D15),"down up up",IF(AND(A15=B15,B15<C15,C15<D15),"same up up",IF(AND(A15=B15,B15>C15,C15<D15),"same down up",IF(AND(A15<B15,B15>C15,C15>D15),"up down down",IF(AND(A15>B15,B15=C15,C15<D15),"down same up",IF(AND(A15<B15,B15>C15,C15=D15),"up down same",IF(AND(A15<B15,B15<C15,C15<D15),"up up up",IF(AND(A15>B15,B15>C15,C15<D15),"down down up",IF(AND(A15<B15,B15>C15,C15<D15),"up down up",IF(AND(A15<B15,B15<C15,C15=D15),"up up same",IF(AND(A15>B15,B15=C15,C15>D15),"down same down", "Do Not Bet"))))))))))))))))))))))))))))

 

[RoryA]

The circular reference tracker is, in my experience, quite infuriating. I often just clear entire columns one by one until it stops reporting a CR, then reinstate the last one deleted and check again. If the CR reappears, at least I know it should be in the column just reinstated.

 

[JeffGrant]

Ok, looks like my primary file has corrupted. I have opened a copy from two days ago and there aren't any c.r.s

I'll work from that file.

thanks for your help.

 

[JeffGrant]

Not sure exactly what you came up with but what about this as a replacement for that very long formula?

JeffGrant.xlsm
	A	B	C	D	E
3	3	2	1	This Race
4	216	240	230	240	up down up
5	210	210	210	208	same same down
6	210	212	210	210	up down same
7	294	210	210	210	down same same
8	210	210	230	218	same up down
9	218	230	218	218	up down same
10	257	212	218	218	down up same
11	210	210	210	208	same same down
12	210	210	210	208	same same down
13	210	210	210	208	same same down
14	272	258	257	258	down down up
15	240	240	216		Do Not Bet
16	240	254	254	254	up same same
17	232	220	224	232	down up up
18	224	216	216	196	down same down
19	212	204	216	230	down up up
20	266	230	232	198	down up down
Sheet1

Cell Formulas
Range	Formula
E4:E20	E4	=IF(COUNT(A4:D4)=4,TEXTJOIN(" ",1,CHOOSE(SIGN(B4:D4-A4:C4)+2,"down","same","up")),"Do Not Bet")

Good Morning Peter_SSs,

I appologise that I didn't look at this last night. I guess I got so carried with with multitude of c.r's that appeared from no where that I had to put everything aside for a while.

I am just in awe of the knowledge in this forum. This formula is fantastic at is the preferred solution that will be implemented today.

Thank you kindly.

 

[Peter_SSs]

You're welcome. Glad it helped.