Within Calc, I’m trying to create a formula that will give me the value from the range A2:A11 that has the greatest magnitude. I believe this should work when entered as an array formula:
=INDEX(A2:A11,MATCH(MAX(ABS(A2:A11)),MAX(ABS(A2:A11)),0))
If I put it in as a normal formula, it says Err:502. If I put it in as an array formula (Ctrl+Shift+Enter), which I beleive is what I should be doing, it says Err:504. Why doesn’t it work as expected?
What’s your concept of “magnitude”? The term seems to be used for the absolute amount.
If so : for what the complications? =MAX(ABS(A2:A11)) should do.
BTW:
MAX() isn’t eligible for array-evaluation. It always converts its arguments into a list.
What I actually want is the value from the range that has the greatest magnitude. Eg., if my range includes the numbers 5, 10, 15, -20, I want to report -20 (preserving the negative).
=INDEX(A2:A11;MATCH(MAX(ABS(A2:A11));ABS(A2:A11);0)) entered for array-evaluation can do if you can assure that the solution will be unambiguous.
If the maximum absolute amount occurs in the list more often than once and with different signs for at least two items, you aim at something not actually existing, but will get the candidate positioned above the others.
Thanks, @Lupp! That worked! I had my original formula wrong, because I was asking the MATCH function to find the index of the max abs in the max abs instead of in the array of abs.