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

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
120
Office Version
  1. 365
Platform
  1. 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


Book2
ABCDE
1
2Numerical Equivalent from All ClassesDecider Equation
3321This Race
4216240230240up down up
5210210210208same same down
6210212210210up down same
7294210210210down same same
8210210230218same up down
9218230218218up down same
10257212218218down up same
11210210210208same same down
12210210210208same same down
13210210210208same same down
14272258257258down down up
15240240216Do Not Bet
16240254254254up same same
17232220224232down up up
18224216216196down same down
19212204216230down up up
20266230232198down up down
Sheet1
Cell Formulas
RangeFormula
E4:E13E4= 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"))))))))))))))))))))))))))))
E14E14= 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:E20E15=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"))))))))))))))))))))))))))))
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Wouldn't it be easier to just test each pair and return up/down/same, then concatenate the results of the three comparisons?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,411
Office Version
  1. 2016
Platform
  1. Windows
If formula too long like this in too many cells, then you have big file size with small contents 😄

I think you should use UDF
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
120
Office Version
  1. 365
Platform
  1. Windows
Wouldn't it be easier to just test each pair and return up/down/same, then concatenate the results of the three comparisons?
Hi Rory, what a simple solution. Works beautifully. Sometimes, I am too close to the trees to see the forest. :)
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
120
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Rory, what a simple solution. Works beautifully. Sometimes, I am too close to the trees to see the forest. :)
Didn't solve the circular reference though :(
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I've never heard of circular references being created just because of too many IFs. Which cell(s) are being flagged as the cause?
 

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
120
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I've never heard of circular references being created just because of too many IFs. Which cell(s) are being flagged as the cause?
This is confusing...
Sheet is "Class Patterns" -> c.r at BQ16.
in BQ16 is =INDIRECT("'Trade'!$H$4")
in Trade H4 is the string "mdn"

Trace Error Check on BQ16 gives message "The trace error command requires that the active cell contain an error value."

There are several of these situations where excel gives a c.r, but there isn't an error at all.

I'm mystified :(
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
A circular reference is not an error per se.

I'd make a copy of the workbook to use for investigation, then delete the contents of Bq16 and see which cell is flagged as the CR now. Continue that pattern until you find the problem cell(s).
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
Hi Rory, what a simple solution. Works beautifully. Sometimes, I am too close to the trees to see the forest.
Not sure exactly what you came up with but what about this as a replacement for that very long formula?

JeffGrant.xlsm
ABCDE
3321This Race
4216240230240up down up
5210210210208same same down
6210212210210up down same
7294210210210down same same
8210210230218same up down
9218230218218up down same
10257212218218down up same
11210210210208same same down
12210210210208same same down
13210210210208same same down
14272258257258down down up
15240240216Do Not Bet
16240254254254up same same
17232220224232down up up
18224216216196down same down
19212204216230down up up
20266230232198down up down
Sheet1
Cell Formulas
RangeFormula
E4:E20E4=IF(COUNT(A4:D4)=4,TEXTJOIN(" ",1,CHOOSE(SIGN(B4:D4-A4:C4)+2,"down","same","up")),"Do Not Bet")
 
Solution

JeffGrant

Board Regular
Joined
Apr 7, 2021
Messages
120
Office Version
  1. 365
Platform
  1. Windows
A circular reference is not an error per se.

I'd make a copy of the workbook to use for investigation, then delete the contents of Bq16 and see which cell is flagged as the CR now. Continue that pattern until you find the problem cell(s).
HI Rory,

this is just silly, it is like I have hundreds of c.r.'s all of a sudden.

for example

c.r in BQ4. in BQ4 is ='Class Patterns'!BX7
delete bq4
then cr in bq5
in bq 5 is ='Class Patterns'!BX8
delete bq5
then cr in am4


in AM4 is "=IF($O$4="", "",$C$4-$O$4)"
in C4 is a date, O4 is either a date or empty.

if I delete am4, excel just picks another cell in the column and says there is c.r in it

and so on and on and on

how is this a c.r.?

thanks
 

Forum statistics

Threads
1,136,792
Messages
5,677,753
Members
419,718
Latest member
ALWP

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.
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 "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
Top