Forum Discussion

Nikoss's avatar
Nikoss
Copper Contributor
Nov 06, 2020
Solved

Need for more than 64 levels of nested - Simplify formula

Hello guys,   I am not that experienced with Excel so I need your help with this. I have a very long formula with 64 levels of nested and I need to add more which is not allowed. I will copy and pa...
admin
BI & Data Analysis
developer
excel
Excel for web
Excel on mobile
Formulas and Functions
  • mtarler's avatar
    mtarler
    Nov 09, 2020

    Nikoss I and re-attaching the sheet.  I deleted the other formulas on that sheet in case there was some confusion.  It works just fine for me.  Maybe you could explain a little more what you mean by it giving the wrong results.  Does it sometimes give correct results?  Does it ever change?  Can you give an example of a wrong result and what it gave and what you wanted it to give?

Nikoss's avatar
Nikoss
Copper Contributor
Nov 06, 2020

Bennadeau Thank you for the prompt response. I have attached a screenshot of it. The values on Column D and E are supposed to be prices but because that is confidential I have replaced them. I hope that helps.

Bennadeau's avatar
Bennadeau
Iron Contributor
Nov 06, 2020

Nikoss 

Try this... 

=IF(EW4<INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Below",IF(AND(EW4>=INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),EW4<=INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0))),"In accordance with Pricing Matrix",IF(EW4>INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Over","NA")))

It is dynamic and flexible as you add more data in your Pricing Matrix sheet.

  • Vrushaket's avatar
    Vrushaket
    Copper Contributor
    Nov 20, 2023
    Please help me simplify following formula. The formula is designed to return specific date, in this case if a date falls in January 2017 it will return 20th of next month and if I write it for 5-6 years it exceeds its limit of 64 nest. Is there any way to simplify this formula?? IF(AND(C189>DATEVALUE("31/12/2017"),C189DATEVALUE("31/01/2018"),C189DATEVALUE("28/02/2018"),C189DATEVALUE("31/03/2018"),C189DATEVALUE("30/04/2018"),C189DATEVALUE("31/05/2018"),C189DATEVALUE("30/06/2018"),C189DATEVALUE("31/07/2018"),C189DATEVALUE("31/08/2018"),C189DATEVALUE("30/09/2018"),C189DATEVALUE("31/10/2018"),C189DATEVALUE("30/11/2018"),C189
  • Nikoss's avatar
    Nikoss
    Copper Contributor
    Nov 06, 2020

    Bennadeau unfortunately I get a #VALUE error with this formula

    • Bennadeau's avatar
      Bennadeau
      Iron Contributor
      Nov 06, 2020

      Nikoss 

      There was a small error in my original formula. Try this one.

      =IF(EW2<INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Below",IF(AND(EW2>=INDEX('Pricing Matrix'!D:D,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),EW2<=INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0))),"In accordance with Pricing Matrix",IF(EW2>INDEX('Pricing Matrix'!E:E,MATCH(G4&I4,'Pricing Matrix'!B:B&'Pricing Matrix'!C:C,0)),"Over","NA")))

      I also attached a sample workbook. Cell L4 is where the formula is. Perhaps I missed something in your explanation so you can see how I based the formula?

       

      I see in your formula that you reference cell G4 for the date and I4 for the model but suddenly, it jumps to cell G2 and I2. I assumed this was a mistake in your formula, let me know if I'm wrong there.

Resources