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?

Bennadeau's avatar
Bennadeau
Iron Contributor
Nov 06, 2020

Hi Nikoss,

This is indeed a very long formula and as you said, there's got to be a way to make it shorter.

Can you share your spreadsheet or at least part of it to better assist you? Make sure there's no confidential data. I'm especially interested in the 'Pricing Matrix' sheet. 

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