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:
Returns as false or " " with ABS when this formula is not calculated in a cell between rows 33-39.
Please Login or Register to view this content.
Please Login or Register to view this content.
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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
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.
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.
Last edited by Tony Valko; 05-28-2015 at 05:29 PM. Reason: added a missing word!
P.S.
That's why this statement is incorrect:
You CAN get a correct result if you don't array enter an array formula....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.
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.
Originally Posted by shg
Tony - I did enter as an array. "I have already done that".
Last edited by TIFinance; 05-28-2015 at 06:24 PM.
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:
^ entered as an array of course.Please Login or Register to view this content.
Good deal. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks