Forum Discussion
Minimum 64 levels of nesting are exceeded in Excel file
I need any suggestion about how can I overcome this problem?
10 Replies
- The problem is that I have 4 digits as like this [0,1,2,3]. They are arranged in four cells in a vertical pattern and there is no restriction that digits will not be repeated more than once.
Out of them I have to make combinations in this way.
1. IF(B8=0,0,
2. IF(AND(B5=3,B6=0,B7=2,B8=2),7,
3. IF(AND(B5=3,B6=2,B7=1,B8=2),7.05,
4. IF(AND(B5=3,B6=1,B7=1,B8=1),7.2,
5. IF(AND(B5=1,B6=1,B7=1,B8=3),7.3,
6. IF(AND(B5=3,B6=2,B7=2,B8=2),7.2,
7. IF(AND(B5=2,B6=3,B7=3,B8=3),6.8,
8. IF(AND(B5=1,B6=2,B7=3,B8=3),6.8,
and so on
The issue is that in this way possible combinations are exceeding beyond 64 levels nesting limit. Plz, guide me how can I make all the combinations and obtain their value without getting 64 levels nesting limit breach
.I would advise you avoid embedding data within formulas in this way. It is possible to build formulas to work on an array of 4 digit rows
but it would be easier if the digits were concatenated to create a 4-digit number
= MMULT(array,10^{3;2;1;0})Based upon the 4-digit numbers, one can filter or search for specific combinations and return associated values.
... continued
IF function – nested formulas and avoiding pitfalls
Instead of nesting, you could use the VLOOKUP () formula
or.. add to complete E.g. If (...) + If (...) + If (...) etc. ( have not tried it, but it should also work)
or... make a custom function out of it.
Additional Information:
Use nested functions in a formula
Hope I was able to help you with this info.
NikolinoDE
I know I don't know anything (Socrates)
Was the answer useful? Mark them as helpful!
This will help all forum participants.
- That depends on the formula. Nested IF formulas can often be replaced by array lookups. Modern Excel (365) also provides LET which allows formulas to be restructured.
As a comment, in question was nothing about IF().
In general Excel has 64 as max limit for nested number of functions. Any functions, just IF() is most common case.
There are alternatives, as for everything in Excel. Which one depends on concrete situation.
- True. It is not a limit I normally hit so I simply took a guess to get the ball rolling
