Solved

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

Posted on 2013-12-13
9
242 Views
Last Modified: 2013-12-23
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”)
0
Comment
Question by:tmaususer
9 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
Hi,

May I suggest this?

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

BFN,

fp.
0
 

Author Comment

by:tmaususer
Comment Utility
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"


Excel example
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
Does that mean an "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? If so try this formula in O63

=IF(SUMPRODUCT((O2:O60>F2:F60)+(O2:O60<E2:E60)),"R","")

If all values are within the range then you get a blank, otherwise an "R"

I assumed your range was row 2 to row 60 (I can't see from the screenshot) - change as required.

If that isn't the requirement can you clarify further. What should be in O63 if all the values are inside the ranges?

regards, barry
0
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:tmaususer
Comment Utility
harryhoudini and Faustulus,
I will past these along and see if these work for them.  
Thanks.
0
 

Author Comment

by:tmaususer
Comment Utility
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
0
 

Author Comment

by:tmaususer
Comment Utility
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")
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
Well done harry! ;)
0
 

Author Comment

by:tmaususer
Comment Utility
Opps,
Sorry about the spelling of you name barryhoudini.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now