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:

INDEX($A$33:$A$39,MATCH(LARGE($I$33:$I$39,K10),$I$33:$I$39,0))

Returns as false or " " with ABS when this formula is not calculated in a cell between rows 33-39.

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

[Tony Valko]

You're testing an array with this:

IF(ABS($I$33:$I$39)>=1%

So, you would have to enter the formula as an array formula.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

[TIFinance]

I could be wrong, but it doesn't seem to be an issue of pushing ctrl, shift, enter because I have already done that. My issue is that the if(ABS()) formula returns "true" when in rows 33-39 and "False" anywhere else. I need it to be in completely different rows though.

[Tony Valko]

For the formula to work properly it has to be array entered as noted in my other reply.

If you're getting a correct result without array entering it's just a coincidence.

If you enter the formula in any cell on rows 33:39 and do not enter it as an array formula, it will evaluate the single cell that is the implicit intersection of the referenced array.

For example, your formula is:

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

If you enter it in cell X36 and do not enter it as an array formula then this expression:

=IF(ABS($I$33:$I$39)>=1%

Is only evaluating the single cell I36. The formula being entered on row 36 so the implicit intersection is cell I36.

[Tony Valko]

P.S.

That's why this statement is incorrect:

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You CAN get a correct result if you don't array enter an array formula.

[MrShorty]

Can you upload a sample workbook illustrating your problem? I see no reason why that formula (array entered) should only work within the 33:39 row range. It seems like it should work just fine from anywhere in the worksheet.

[TIFinance]

Tony - I did enter as an array. "I have already done that".

[TIFinance]

Solved as follows. I need the LARGE formula to only take into consideration values in I33:I39 which meet the specified condition. Apparently, the condition needs to be placed within the actual LARGE function, not preceding it:

=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))

^ entered as an array of course.

[Tony Valko]

Good deal. Thanks for the feedback!