How do I calculate Absolute Value in a cell not aligned with rows used in the array?

[TIfinance]

Is there a way to calculate Absolute Value in a cell that's not aligned with the rows used in the array? I'm using ABS(I33:39) but it only calculates if the cell I place the ABS formula in is somewhere in rows 33-39.

Here are the formula's I'm using...
Works just fine in any row:
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">INDEX($A$33:$A$39,MATCH(LARGE($I$33:$I$39,K10),$I$33:$I$39,0))</code>
Returns as false or " " with ABS when this formula is not calculated in a cell between rows 33-39.
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">

=IF(ABS($I$33:$I$39)>=1%,INDEX($A$33:$A$39,MATCH(LARGE($I$33:$I$39,K10),$I$33:$I$39,0)),"")</code>

 

[XOR LX]

Hi.

It appears that you wish the LARGE formula to only take into consideration values in I33:I39 which meet your specified condition, correct? If so, that condition should be placed within the actual LARGE function, not preceding it, i.e.:

=INDEX($A$33:$A$39,MATCH(LARGE(IF(ABS($I$33:$I$39)>=1%,$I$33:$I$39),K10),IF(ABS($I$33:$I$39)>=1%,$I$33:$I$39),0))

which also requires committing as an array formula**.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[TIfinance]

Thanks! Problem solved

 

[XOR LX]

You're welcome!