First of all, I would like to clarify that I am looking for a technical explanation as to why the following issue occurs, rather than for alternative set-ups which achieve the desired result (I shall in any case present several such alternatives as part of this question).
Also, I have not tested this in versions of Excel other than Excel 2010, so am not aware whether this issue is version-specific or not.
Row/ B C
Col
1 X 91
2 X 96
3 X 21
4 X 64
5 3
6 X 60
7 52
8 X 37
9 79
10 91
Assuming all entries are made in Sheet1, then given, for example, a series of numerics in the range C1:C10, and with each entry in the range B1:B10 being either "X" or not (example given above), the following construction:
=INDEX($C:$C,N(IF(1,MODE.MULT(IF($B$1:$B$10="X",{1,1}*ROW($B$1:$B$10))))))
properly coerced, will generate a vertical array of n entries, where n is equal to the result of:
=COUNTIF($B$1:$B$10,"X")
This array will comprise the n entries from column C whose corresponding entry in column B is "X". For the above example, this construction would return:
{91;96;21;64;60;37}
This formula is then entered as a (Workbook-scope) Defined Name within Name Manager, e.g. with Name Series_MODE_MULT. Note that, being a Defined Name, the construction is by default evaluated as an array formula, and so satisfies the condition of being "properly coerced" which I mentioned earlier.
When attempting to create a simple chart (Column, for example) with a single series, whose Series Values are entered as:
=Sheet1!Series_MODE_MULT
Excel immediately crashes.
But why?
I have searched for an explanation for some hours now. The only similar cases I can come up with relate to the use of INDIRECT within the construction being passed as the Chart's Series Values; however, although that too is invalid (we can in fact develop a workaround using EVALUATE), the user is met with the error message "A formula in this worksheet contains one or more invalid references.", which, of course, is not at all the same as flat-out crashing.
My title suggests that the issue might be specifically related to the function MODE.MULT. My reasoning is that we can employ alternative (though longer and, in some cases, volatile) constructions which generate the required array but which are perfectly valid when passed as the Chart's Series Values:
=INDEX($C:$C,N(IF(1,SMALL(IF($B$1:$B$10="X",ROW($B$1:$B$10)),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIF($B$1:$B$10,"X")))))))
or:
=N(OFFSET($C$1,SMALL(IF($B$1:$B$10="X",ROW($B$1:$B$10)-MIN(ROW($B$1:$B$10))),ROW($A$1:OFFSET($A$1,COUNTIF($B$1:$B$10,"X")-1,))),))
being just two such examples.
Is MODE.MULT somehow responsible here? What makes this construction valid within the worksheet though not within a Chart's Series Values? Are there any other constructions/functions which, used in this way, similarly cause Excel to crash? If so, what is the explanation for such behaviour?
Any help would be greatly appreciated.
Regards