Passing multiple ranges as AGGREGATE's array parameter

user_3314095 0
Viewed 759

I would be grateful if anyone knows whether the following issue is documented and/or what the underlying reasons are.

Assuming we have, for example, the numbers from 1 to 10 in A1:A10, the following formula

=SUMPRODUCT(SUBTOTAL(4,OFFSET(A1,{0;5},0,5)))

is perfectly valid and is equivalent to taking the sum of the maximum values from each of the ranges A1:A5 and A6:A10, since the OFFSET function, here being passed an array of values ({0;5}) as its rows parameter and with the appropriate height parameter (5), resolves to the array of ranges:

{A1:A5,A6:A10}

which is then passed to SUBTOTAL to generate a further array comprising the maximum values from each of those ranges, i.e. 5 and 10, before being summed by SUMPRODUCT.

AGGREGATE was introduced in Excel 2010 as, it would seem, a more refined version of SUBTOTAL. My question is why, when attempting the following

=SUMPRODUCT(AGGREGATE(14,,OFFSET(A1,{0;5},0,5),1))

which should be equivalent to the SUBTOTAL example given above, does Excel display the message that it "Ran Out of Resources While Attempting to Calculate One or More Formulas" (and return a value of 0)?

(Note that users of a non-English-language version of Excel may require a different separator within the array constant {0;5}.)

This is a quite unexpected error. Evidently the syntax is not at fault, nor is the passing of the OFFSET construction 'disallowed'. With nothing else in the workbook, what is causing Excel to use so much resource when attempting to resolve such a construction?

A similar result occurs with INDIRECT instead of OFFSET, i.e.

=SUMPRODUCT(SUBTOTAL(4,INDIRECT({"A1:A5","A6:A10"})))

is perfectly valid, yet

=SUMPRODUCT(AGGREGATE(14,,INDIRECT({"A1:A5","A6:A10"}),1))

gives the same error described above.

Regards

excel-2016 excel-formula excel arrays
2 Answers
user_60844090

The AGGREGATE error appears to be due to passing an array of range references to an argument that expects an array of values. The error message has symptoms of passing an unitialized pointer resulting in unexpected behavior. Indeed, the same error dialog is shown with some other functions like:

=MEDIAN(TRANSPOSE(INDIRECT({"a1:a5","a6:a10"})))

On the other hand, passing an array of references to the fourth or later argument of AGGREGATE is permitted, eg:

=SUMPRODUCT(AGGREGATE(4,,B1,INDIRECT({"a1:a5","a6:a10"})))

In a similar way, SUBTOTAL allows arrays of references in the second or later arguments, none of which natively take arrays. The SUBTOTAL formula is evaluated by applying the function to each range reference in the array, i.e.:

   SUBTOTAL(4,INDIRECT({"a1:a5","a6:a10"}))
->{SUBTOTAL(4,A1:A5),SUBTOTAL(4,A6:A10)}

Formatting arrays and range references within function definitions may help with visualising the formula processing:

AGGREGATE(function_num, options, array or ref1, [k or ref2], [ref3], …)

SUBTOTAL(function_num, ref1, [ref2],...)

Note that reference only arguments also allow for arrays of references.

It will be interesting to see if there are any changes to this behavior with the updated calc engine and dynamic arrays currently in Office 365 preview and due for release soon...

user_12528200
Related