To enter an array formula in the formula bar, you cannot type the curly brackets { }....those need to be inserted by Excel by entering the formula with Ctrl-Shift-Enter. But within a formula, if you want to create an array, you can type the curly brackets, which is what we have in:
Excel Formula:
MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})
This multiplies two arrays by each other...but to answer your question better, a more detailed explanation is needed.
Let's start with the AGGREGATE function tucked inside the main formula in three places:
Excel Formula:
AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12))))
The AGGREGATE function operates on an array, and while it can perform many different operations on that array, in this case, we want to use it to return a list of row indexes where the parcel shown in $C$12 is found in column U of your main data table. And in this case, for convenience, we want the row indexes returned in order, from smallest to largest, much like you would do if manually searching down column U from the top to the bottom. Used like this, AGGREGATE takes 4 arguments:
The 1st argument is code 15, which tells AGGREGATE to return the smallest values first, and you have an option to return the 1st smallest, 2nd smallest, 3rd smallest, etc. That is handled by the last argument of the AGGREGATE function (more on that in a moment).
The 2nd argument (6) tells AGGREGATE to ignore any errors that are generated. This is a good thing because we will intentionally create some errors to find the matches (explained next).
The 3rd argument in the AGGREGATE function divides one array by another...ridx/(INDEX(data,,21)=$C$12)
Let's break this down further. Recall that the named variable "ridx" (row indexes) is formed by the formula:
Excel Formula:
ROW($X$2:$X$7000)-ROW($X$2)+1
which will result in an array that looks like {1;2;3;...6998;6999}. This is a single vertical array that has row index numbers corresponding to rows 2:7000 in your data table.
Then recall we have a named variable called "data" that refers to cells A$2:$AI$7000 in the main data table. The INDEX function used here:
returns column index 21 from "data", and since "data" begins at column A, column index 21 is the same as the 21st column, or column U. So this part of the function looks down column U and determines whether any of the parcels shown match the one specified in $C$12. The result will be a vertical array consisting of 6999 rows, and each row contains a value of either TRUE or FALSE, with TRUE indicating that a match is found at that position. The 3rd argument divides the first array by the second, which would look something like this:
{1;2;3;...6998;6999} / {FALSE;TRUE;FALSE;...TRUE,FALSE}
and when Excel performs arithmetic operations on arrays, the TRUE's are converted to 1's and the FALSE's to 0's, so this produces a resultant array that looks like this:
{#DIV/0!;2;#DIV/0!;...6998;#DIV/0!}
where the division by 0 errors indicate that there is no match at that location, and any other values indicate that a match was found at that row index location. This is the advantage of using AGGREGATE with the 6 option to ignore errors, and this allows us to filter to retain only the row indexes that we want.
The 4th argument of the AGGREGATE function indicates which of the values to return from the resultant array. Since the 15 code means return smallest first, a 1 as the 4th argument returns the absolute smallest value, a 2 the next smallest, etc. We want all of the row indexes reported back to us, so we need to know how many were found. So we count them with:
Excel Formula:
COUNTIF(INDEX(data,,21),$C$12)
which looks at column U of the main table and counts the number of times a match is found with the value in $C$12. We then feed that number into this function...
Excel Formula:
ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))
...to create a sequential dynamic array beginning with the number 1 and ending with the number determined from the COUNTIF function. Let's suppose COUNTIF finds that there are 2 rows containing transaction details for a parcel on $C$12. So the 2 is fed into the ROW(INDIRECT function and an array is returned that looks like {1;2}...a two row array consisting of sequential numbers beginning with 1.
The end result then is that the AGGREGATE function returns an array of row index numbers, ordered from smallest to largest, where matches were found, and the array in the 4th argument says to return the 1st smallest value, then the 2nd smallest value, etc. until all values have been returned. That's what the {1209;1876;2706} indicates in your last post...so the COUNTIF function found that there are three matches, and those matches are found at the row indexes shown (which would correspond to row numbers 1210, 1877, and 2707.
This is the type of result generated by the AGGREGATE function in the 1st line of the main formula...an array of row index numbers where matches exist.
But there is much more to do...we still need to determine which of those row indexes has the most transactions. So we repeat the AGGREGATE function and then operate on the resulting array a little further...I'm considering this part which appears in the 2nd and 3rd lines of the main formula:
Excel Formula:
INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})
To simplify the appearance, and since we already know what AGGREGATE returns, let's substitute in a realistic array for it, like the one in your last post:
INDEX(data, {1209;1876;2706}, {8,9,10,11,12,13})
This tells the INDEX formula to access the main data table $A$2:$AI:$7000 and return the intersection of row indexes {1209;1876;2706} with column indexes {8,9,10,11,12,13}. This is like copying the range H:M (columns indexes 8-13) for rows 1210, 1877, and 2707 and creating a small mini-table of those values, 3 rows long and 6 columns wide. Let's call this the mini-matrix of dates and amounts, or "mmda" for short. We know that some rows of mmda may not be filled out completely, and we want to determine which one has the most transactions. So we evaluate each element in the mmda matrix to determine if the value is NOT equal to 0 (or NOT a blank):
Excel Formula:
(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0)
which is is equivalent to
mmda <> 0
The 3 x 6 matrix of values (of dates, payment amounts, and blanks) will now have the same structure, but will consist of TRUE's and FALSE's, with TRUE's indicating that transaction data exist. We need to convert these TRUE's and FALSE's to 1's and 0's for the next step, so we apply the double unary operator to convert the TRUE/FALSE matrix into a matrix of 1's and 0's. That is equivalent to --(mmda <> 0).
Almost done! This big expression...
Excel Formula:
(MMULT(--(INDEX(data,AGGREGATE(15,6,ridx/(INDEX(data,,21)=$C$12),ROW(INDIRECT(1&":"&COUNTIF(INDEX(data,,21),$C$12)))),{8,9,10,11,12,13})<>0),{1;1;1;1;1;1})
can now be thought of like this:
Excel Formula:
(MMULT(--(mmda <> 0), {1;1;1;1;1;1})
...for the example in your last post, a small 3 x 6 matrix of 1's and 0's will be multiplied by a column array consisting of 6 1's (this is what you asked about). But why? We want to count across each of the rows in the 3 x 6 matrix to determine how many 1's there are...or since they are just 1's, we can add across each row. That tells us, of the 6 columns H:M in the data table, how many of those 6 columns contain transaction data. But your version of Excel does not offer a direct way to perform this across-the-row addition, so matrix multiplication provides a solution. Each element across the row is multiplied by the corresponding vertical array element in the {1;1;1;1;1;1} array and then those 6 multiplication products are added together. That single number tells us the number of 1's on the row. This is repeated for each row to produce the sum on each, so the end result is a 3 row by 1 column array with the number of transaction-related entries corresponding to each row in the mmda matrix. Here is a visual showing what the matrix resulting from (--mmda <> 0) might look like multiplied by the vertical array of 6 1's that you asked about, and this would evaluate to {2;4;6}, as shown:
| MrExcel_20220610.xlsx |
|---|
|
|---|
| G | H | I | J | K | L | M | N | O | P | Q | R |
|---|
| 102 | | | | | | | | | | | | |
|---|
| 103 | | | | | | | | | | | | |
|---|
| 104 | | 1 | 1 | 0 | 0 | 0 | 0 | | 1 | | 2 | |
|---|
| 105 | | 1 | 1 | 1 | 1 | 0 | 0 | * | 1 | = | 4 | |
|---|
| 106 | | 1 | 1 | 1 | 1 | 1 | 1 | | 1 | | 6 | |
|---|
| 107 | | | | | | | | | 1 | | | |
|---|
| 108 | | | | | | | | | 1 | | | |
|---|
| 109 | | | | | | | | | 1 | | | |
|---|
| 110 | | | | | | | | | | | | |
|---|
|
|---|
We know we want the row index holding the most transactions, so you will see the same formula just described wrapped by a MAX function so that it returns a single value...in this case a 6.
That's all the 3rd line of the main formula does...it returns the number of the most non-blank/non-zero cells on a row in the mmda matrix.
Now in your latest post, let's suppose row indexes {1209;1876;2706} contain 2, 4, and 6 non-blank cells, respectively...so we can tell that we would like to identify row index 2706 as the one from which to pull all of the values. Putting it all together, for the example in your last post and with this assumption, the main formula would look like:
=MAX( {1209;1876;2706} * ( {2;4;6} = MAX({2;4;6}) ) )
which becomes
=MAX( {1209;1876;2706} * ( {2;4;6} = 6 ) )
and then evaluating the 2nd term involving the logical = we would get:
=MAX( {1209;1876;2706} * {FALSE; FALSE; TRUE} )
because only the last element in the {2;4;6} array evaluates as TRUE (that is the MAX value).
Finally, we multiply these two arrays together, recalling that TRUE's are coerced to 1's and FALSE's to 0's, so we get:
=MAX( {0; 0; 2706} )
and the MAX of that array is row index 2706. That's how the main formula works, and it appears to me that you are getting expected results for terms in this formula, so I suspect something else is wrong.
I don't know of a good way to troubleshoot the issue you seem to be having without seeing the file. If you would like, you could create a copy of your workbook. Delete all worksheets except for the test Tax Cert Form that you have been working with. Delete any sensitive information on that sheet, but leave the formulas that have been discussed intact. Then on the data sheets, delete any sensitive data...I really only need columns H:M and U for debugging, but you can leave more if desired. Then upload that file to a file sharing site like Dropbox, Drive, Box, etc. and provide a sharing link.
