Dec 22 2021 11:04 PM
I need any suggestion about how can I overcome this problem?
Dec 22 2021 11:25 PM
Dec 22 2021 11:37 PM
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.
Dec 23 2021 05:19 AM
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.
Dec 23 2021 08:46 AM
Dec 23 2021 09:11 AM
Dec 23 2021 10:26 AM - edited Dec 23 2021 10:35 AM
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.
Dec 23 2021 10:53 PM
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.
Dec 24 2021 12:09 AM
Dec 24 2021 02:08 AM
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!