Forum Discussion
Need for more than 64 levels of nested - Simplify formula
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?
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.
Bennadeau yes that was a mistake from my end, which I noticed the moment I posted the question, but I didn't want to make things more complicated, that is why I didn't mention it and corrected the mistake on my end. Maybe there is a mistake on the sample you sent me because I don't see the formula on L4. L4 formula is "=I17" and the result is "0" (since I17 is blank).
I am sure you have understood by now what I am trying to do but just to be 100% sure here is an explanation based on the sample you sent me:
1. If "G4" matches any of the cells from Pricing Matrix Column B, "I4" matches any of the cells from Pricing Matrix Column C and "EW2" is smaller than Pricing Matrix cell "D4" then the result should be "Below"
2. If "G4" matches any of the cells from Pricing Matrix Column B, "I4" matches any of the cells from Pricing Matrix Column C, "EW2" is equal or greater than Pricing Matrix cell "D4" and equal or smaller than Pricing Matrix cell "E4", then the result should be "In Accordance with Pricing Matrix"
3. If "G4" matches any of the cells from Pricing Matrix Column B, "I4" matches any of the cells from Pricing Matrix Column C and "EW2" is greater than Pricing Matrix cell "E4" then the result should be "Over"
I have attached your sample worksheet as well. I have copied and paste your formula on EX2 and as you can see I get the same #VALUE! error.
I really appreciate your help with this Bennadeau
Thank you,
- Nikoss
Some weird thing happened with that formula. I'm uploading a revised version of the doc. Hopefully it will work fine now.Bennadeau although it works on the sample sheet it doesnt work on my original one for some reason so something is wrong with the original one probably. Again, I would like to thank you for your help on this, it is very much appreciated!
You may want to check to make sure your cell formatting match.
Number formatting when only numbers are present
Currency when it's a $ amount
Text for everything else.
In both sheets of course.
Also, ensure there is no trailing space in your cells (white space at the end or "2016" for example). This will mess up things.
Nikoss Alternatively I think this works:
=CHOOSE(IFERROR(SUM(--((FILTER('Pricing Matrix'!D:E,Sheet1!G4&Sheet1!I4='Pricing Matrix'!B:B&'Pricing Matrix'!C:C,"NA")-EW4)>=0))+2,1),"NA","Over","In accordance with Pricing Matrix","Under")see attached sheet.
Also, you can make use of the IFS() function to eliminate nested IF() statements.