Minimum 64 levels of nesting are exceeded in Excel file

[ALI12345]

I need any suggestion about how can I overcome this problem?

[#2]

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.

[#3]

@ALI12345 

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

Video: Nested IF functions

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.

[#4]

@Peter Bartholomew 

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.

[#5]

True. It is not a limit I normally hit so I simply took a guess to get the ball rolling

[#6]

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
.

[#7]

@ALI12345 

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.

[#8]

... continued

[#9]

I have understood what you want to say. But I want to know that what you are trying to achieve with this = MMULT(array,10^{3;2;1;0})Plz guide. It will be of much help for me. And further If you can tell how can I make the data chart as you have shown, it will be really helpful for me. I have understood what you want to say. But I want to know that what you are trying to achieve with this = MMULT(array,10^{3;2;1;0})Plz guide. It will be of much help for me.And further If you can tell how can I make the data chart as you have shown, it will be really helpful for me.

[#10]

@ALI12345 

As variant of what @Peter Bartholomew  suggested

[#11]

@ALI12345 

Hi. the original Nx4 array are simply values extracted from your formula.  I then decided that more options for processing would be available if the 4 digits comprising each row were encoded to give a single value.  The obvious encoding for 4 digits is to convert them into a number in the range 1111 to 9999.  As used in the form

 = MMULT(array,10^{3;2;1;0})

the mathematics function MMULT takes each row of the array and multiplies term by term by {1000,100,10,1} and adding to give a 4-digit number.  The effect of this transformation is that searches for the array {3,1,1,1} (say), which would have required 4 separate comparisons reduce to the easier problem of searching for the number 3111.

In @Sergei Baklan 's post he demonstrates that it is quite possible to compare the original 4 digits and combine the results to identify matches.  Often, there are multiple approaching to solving a problem, each with their own strengths and weaknesses.

A further trivial point that would not be obvious from the pictures of the worksheet that I posted was the fact that the values shown in the form 3,1,1,1 are not comma-separated values, as they appear, but, rather, are numbers such as 3111 (three thousand, one hundred and eleven) but with a custom number format "0\,0\,0\,0" which inserts hard-coded commas into the displayed number.  The lesson there is 'do not trust what you see' when it comes to spreadsheet displays.  Formatting can be used to convey relevant information but it can also be used to conceal and deceive!