Forum Discussion
Minimum 64 levels of nesting are exceeded in Excel file
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
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.
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 SergeiBaklan '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!
