Link to home
Start Free TrialLog in
Avatar of tmaususer
tmaususerFlag for United States of America

asked on

Determining If a Value Is Outside of a Given Range and Flag a Cell If True in Excel 2010

Hello,
I have a user who wants to have value compared two values that determine a range.  If the value is greater than or less than the range between the two other values.  He would like to flag an entirely different cell than the one containing the formula.

Here is a clearer example of the logic:

Logic hoping to achieve
If (O4) is less than (E4) OR (O4) is greater than (F4) then (O63),“R”

Incorrect Excel function
=IF (O4) < (E4) OR > (F4),(O63,”R”)
Avatar of [ fanpages ]
[ fanpages ]

Hi,

May I suggest this?

=IF(OR(O4<E4,O4>F4),O63,"R")

BFN,

fp.
Avatar of tmaususer

ASKER

I like this.  I think it is very close.  I tried it and discovered I may have not explained my situation well.  I have attached a picture to help.  

For example, they want to enter a value in cell O49 (red number) and if it is out side the range between cells E49 and F49 (both yellow), then they want cell O63 (circled in blue) to have a value of "R"


User generated image
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perhaps the solution is quite simple:-
[O63] =IF(OR(O4<E4,O4>F4),"R","")

Open in new window

(1) Excel worksheet formulae can't write anything to any cell. Instead, they can display very nearly anything you can imagine in the cell where they reside themselves. This means, if you wish to display something in cell O63 you must plan on placing your formula in that cell and look at the worksheet from there.
(2) From that vantage point it becomes clear that you must not only decide what to write in the cell when your conditions are met but also what to display when they are not. For this purpose Excel provides the IF function.
IF ([condition], [True], [False])
In other words, if the condition evaluates to True the function displays one result, otherwise (since it must be False where it isn't True) another result.

In the function I have suggested above, OR(O4<E4,O4>F4) is the condition. Note how the components are separated by commas. You could enumerate more, but all of them are within parenthesis which are preceded by the function's name (OR) as is Excel's wont.

O63 doesn't appear anywhere in the function because that destination is implied by the location of the formula. Instead, it specifies that nothing ("") should be shown if the condition doesn't evaluate to True.

I hope this helps.
harryhoudini and Faustulus,
I will past these along and see if these work for them.  
Thanks.
harryhoudini,
you are correct:
"R" should appear in O63 if any of the column col O values appear outside the col E/col F range for that respective row
Thank you harryhoudini!
This is what ended up working:
=IF(SUMPRODUCT((O5:O53>$F$5:$F$53)+(O5:O53<$E$5:$E$53)),"R","A")
Well done harry! ;)
Opps,
Sorry about the spelling of you name barryhoudini.