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

#### TIfinance

##### New Member
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>

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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).

Thanks! Problem solved

You're welcome!

Replies
0
Views
544
Replies
3
Views
528
Replies
9
Views
574
Replies
12
Views
931
Replies
9
Views
387

1,196,273
Messages
6,014,383
Members
441,818
Latest member
itsfaisalkhalid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back