4

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

Improve this question
4
  • I think even if you were able to get it to work, since Aggregate is an array type formula in and of itself it would only return 10. For example =SUMPRODUCT(AGGREGATE(14,6,CHOOSE({1,2},A1:A5,G1:G5),1)) will always return the max between the both ranges, with or without the sumproduct. The aggregate, unlike the Subtotal, iterates inside itself and returns only one answer to the SUMPRODUCT. Commented Oct 20, 2018 at 16:00
  • @ScottCraner But there is a big difference in your choice of CHOOSE (in which the two ranges are first resolved to a single array of values prior to being passed to the outlying function) and OFFSET and INDIRECT, which can be used to generate an array of ranges to pass to functions such as SUBTOTAL and COUNTIF. And can you clarify what you mean by "The aggregate, unlike the Subtotal, iterates inside itself"? Is there some supporting documentation for this technical explanation? And why does this only apply to the range parameter? Commented Oct 20, 2018 at 16:17
  • No supporting documents. As to why, I started the comment, I think.... I was just spit balling, which is why it is a comment and not an answer. Sorry, could not be more help. There are very few people on this board that understand the workings of formulas better than you. I will be following this thread, and hope someone who understands the inner working of AGGREGATE answers. Commented Oct 20, 2018 at 16:28
  • Your comment is helpful, and it may well turn out that you are correct re AGGREGATE. However, I'm more interested in an explanation as to (1) the difference between the behaviours of SUBTOTAL and AGGREGATE with respect to processing multiple ranges and (2) why AGGREGATE should cause Excel to "run out of resources" in such situations. Commented Oct 20, 2018 at 21:06

2 Answers 2

Reset to default
2

[Not enough reputation to add a comment.]

Excel on Mac returns this:

Arrays containing ranges are not supported

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Interesting, thanks. Excel for Windows does not consider this formula to contain any 'errors', so I'm fortunate that someone using Excel for Mac has responded, You've confirmed what I suspected, i.e. that the AGGREGATE function was designed without the ability to process arrays of ranges.
Of course, it still doesn't explain why the construction is allowed syntactically (I presume it's not in Excel for Mac?), yet causes Excel to run out of resources.
It's allowed in Mac Excel, but results in an error:
There are other functions that could work properly or does shows up in excel, but when you try to execute them, they result in an error (the interesting part is that they show correct value if I open the workbook... until i enter the cell). Example is new functions in excel 2016 but is not fully supported in excel 2010.
1

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...

Improve this answer

2 Comments

Extremely useful, thanks. And very interesting that we can pass arrays of references beyond the third argument. Re your explanation, it nevertheless seems a little surprising that AGGREGATE still expects an array of values even when the reference version of that formula is selected (which can be made explicit via use of the Insert Function dialog). Hence I'm not quite sure what to make of your array or ref1 definition, since this would seem to be conveniently combining the two versions.
Another point of note is that the data types shown next to arguments in the function wizard are the same whichever function form is selected. Arguments can be any combination of value (numbers, text, logical, any), reference and/or array type. But if a function can take multiple types the function wizard only shows one type and seems to display them in the order 1. value 2. reference 3. array. So where a function can take a reference or array like AGGREGATE, INDEX or FREQUENCY it always shows "reference", and where a function can only take arrays like SUMPRODUCT or MMULT, "array" is shown.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.