ROW(A10:A20)
(ROW(A10:A20))
INDEX(MAX((F13:F19=E13)*(ROW(F13:F19))),0,0)
And in your Index/Max example, the formula is multiplying one array with another array, which returns an array of results.
They are still all arrays.
the brackets are irrelevant in that formula.
the ROW function is being force to return an array by the array function MAX
Now for the example '=INDEX(MAX((F13:F19=E13)*ROW(F13:F1 9)),0,0)' the ROW function is being force to return an array by the array function MAX - if it didn't then multiplying it's return by the array would produce different results. This suggests that it is not entirely context but also some other factor.
SUM(ROW(DATA))
... returns '1' , whereas ...SUM(INDEX(ROW(DATA),0,0))
... returns '28'. Because 'INDEX' expects and uses an array as a parameter the ROW function has been 'forced' to return / use an array (probably by iterating through the single cells in it's parameter). { SUM(ROW(DATA)) }
... then again you get '28' which has appears to have iterated the scalar version of the ROW formula (as above) before summarizing the result. SUM(C3:C7)
An adaptation to that formula intended to multiply each cell in the range by 6 - it will produce #VALUE ...SUM(C3:C7*6)
.. it appears that Excel cannot resolve this as an array as it is an 'unequal array'. If you use INDEX (around 'C3:C7*6') to accept and return an array or use CSE, then it does work.If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Location of files for Quick Access Toolbar (QAT) settings in Office 2010 | 3 | 36 | |
Manipulate Range in Excel VBA | 6 | 24 | |
Excl VBA Find last column in disjointed range selection | 18 | 22 | |
Excel VBA Find Lowest Column number in any range selection | 5 | 22 |
Join the community of 500,000 technology professionals and ask your questions.