r/excel•
ARCDad

If Value falls in numeric range, then OK, if not then Review

Waiting on OP

I need to create a formula that tells me if the number forecasted (in Column E) falls in between the values in Columns F and G. However I'm drawing a blank on how to do this right now.

Posted Oct 1, 2020, 1:07 PM UTC

111
LeftChoux4•

Another way would be:

=IF(AND(E2>=MIN(F2:G2),E2<=MAX(F2:G2)),"Ok","Review")
2
regxx152•

That is a great way also 👍

1
AutoModeratorMOD•

u/ARCDad - please read this comment in its entirety.

Once your problem is solved, please reply to the answer(s) saying Solution Verified to close the thread.

Please ensure you have read the rules -- particularly 1 and 2 -- in order to ensure your post is not removed.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1
regxx152•
=IF(AND(E2>=G2,E2<=F2), "OK", "Review")
1
DeucesWild_at_yss187•

u/fuzzy_mic has it 100% spot on regardless of the layout!

Both of our formulas are correct but also incorrect depending on how the OP has the values. If F is the smaller and G is the larger, then your formula will not work - mine will, and likewise, if F is the larger and G is the smaller, yours will work - mine wont.

This is all dependent on the OPs layout.

2
fuzzy_mic485•

= IF((E2-G2)*(E2-F2)<0, "OK", "Review")

4
DeucesWild_at_yss187•

YOU WIN! I hadn't even thought about doing it this way LOL!!

1
LeftChoux4•

= IF((E2-G2)*(E2-F2)<0, "OK", "Review")

I like this. Very innovative solution.

1
regxx152•

u/fuzzy_mic has it 100% spot on...

I'd have been disappointed if he hadn't 😫👍

1
DeucesWild_at_yss187•

u/fuzzy_mic has the spot on answer regardless of the layout. Removed (technically) my answer.

Not knowing where you want the result to show, I chose H but it doesn't matter - the formula doesn't care where it resides ;)

=IF(AND(E1>=F1,E1<=G1),"OK","Review")

What that will do is look at the value of E1. If it is greater than or equal to F1 (the lesser value) AND it is less than or equal to G1 (the larger value) then H1 says In Range (true), and Not In Range (false)

1
Decronym•

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

|Fewer Letters|More Letters| |-------|---------|---| |AND|Returns TRUE if all of its arguments are TRUE| |IF|Specifies a logical test to perform| |MAX|Returns the maximum value in a list of arguments| |MIN|Returns the minimum value in a list of arguments|


^(Beep-boop, I am a helper bot. Please do not verify me as a solution.)
^(4 acronyms in this thread; )^(the most compressed thread commented on today)^( has 13 acronyms.)
^([Thread #946 for this sub, first seen 1st Oct 2020, 13:48]) ^[FAQ] ^([Full list]) ^[Contact] ^([Source code])

1