On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
Become a Premium Member and unlock a new, free course in leading technologies each month.
ROW(A10:A20)
(ROW(A10:A20))
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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 |
---|---|---|---|
please help modify and explain this VBA | 13 | 60 | |
need copy macro to select range to copy | 23 | 47 | |
Excel to show a dynamic Picklist at level2 | 2 | 21 | |
how to read data from all excel sheet and copy it in in Master sheet | 2 | 22 |
Join the community of 500,000 technology professionals and ask your questions.