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

Resources