JeffGrant
Active Member
 Joined
 Apr 7, 2021
 Messages
 299
 Office Version

 365
 Platform

 Windows
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
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")))))))))))))))))))))))))))) 