Hello Experts,
How do you obtain the nth value within a formula or calculation array range?
This basic method works because it is referencing a range $A$1:$A$4
However, I will not be referencing a range, nor helper columns...I'd like the have each formula within $A$1:$A$4 inside of SMALL and RANK.Please Login or Register to view this content.
For example: (Invalid array range error)
RANK(A1,(1+1, 2+2, 3+3, 4+4),1)
SMALL( (1+1, 2+2, 3+3, 4+4),1)
Attempted with RANK(A1, {1+1, 2+2, 3+3, 4+4}, 1) didn't work either
To get the nth value, I was thinking about using SMALL & RANK. But, this way doesn't work
SMALL( (1+1, 2+2, 3+3, 4+4), RANK(A1,(1+1, 2+2, 3+3, 4+4),1) )
Is there a better option to get the formula calculation inside of the array range?
I've tried AGGREGATE also but that, too, resulted in an Invalid array error.
Strangely, MIN and MAX does allow formulas & calculations such as
MIN (1+1, 2+2, 3+3, 4+4)
Thanks in advance,
Ricky
Bookmarks