IF-THEN Statement for Determining if a # Lies Between 2 Other Numbers
Posted by Stephen E. Anderson on April 14, 2001 1:00 PM
Is there a function that will allow me to determine if a number lies between 2 other numbers? I'm writing an IF-THEN function and want to know if "2" (in one cell) lies between "1" and "10" (both in other separate cells).
Thanks for your help!
Posted by Aladin Akyurek on April 14, 2001 1:12 PM
Lets say that the value you want to evaluate is in A1, the others in B1 and C1.
In A2 enter: =IF(AND(A1>=MIN(B1,C1),A1<=MAX(B1,C1)),1,0)
You may replace 1 by "Yes" and 0 by "No", where 1/Yes means that the target number lies in between the other two.
Aladin
Posted by Mark W. on April 14, 2001 1:30 PM
Yet another way...
...using an array formula (entered using
Control+Shift+Enter):
{=PRODUCT(B1:C1-A1)<=0}
Posted by Dave Hawley on April 14, 2001 8:28 PM
Re: Yet another way...
Stephen, I would use Aladins formula if I were you, as Marks is flawed. It will return TRUE if you number is equal to the Upper or Lower comparison value. Besides it's an incorrect use of an array formula.
Dave
OzGrid Business Applications
Posted by Mark W. on April 15, 2001 12:25 AM
Re: Yet another way...
> It will return TRUE if you number is equal
> to the Upper or Lower comparison value.
So does Aladin's. Dave aren't you capabable of reading and interpeting an Excel formula by now? Better take a refresher course in Excel 101. As is the case for Aladin's formula if one wants to evaluate an exclusive "between" condition the = comparison operator can be removed.
> Besides it's an incorrect use of an array
> formula.
Only in your narrow mind.
Posted by Dave Hawley on April 15, 2001 2:41 AM
Re: Yet another way...
>So does Aladin's
Pointing out Aladins mistakes wont shift the focus, you stuffed up.
>Only in your narrow mind
That a bit rich coming from somebody who uses array formulas (constantly) to determin the values of 3 or less cells.
You really need to learn to swallow you own medicine marky. As the saying goes, "if you can't take it, don't dish it out" LOL!
Dave
OzGrid Business Applications
Posted by Aladin Akyurek on April 15, 2001 12:11 PM
Propose that ...
you start your own board. You might even consider calling it:
Dave's Ultimate Message Board.
====================== >So does Aladin's Pointing out Aladins mistakes wont shift the focus, you stuffed up.
Posted by Dave Hawley on April 15, 2001 2:52 PM
Re: Propose that ...
Aladin. I propose that if you too cannot take it, then don't dish it out!
OzGrid Business Applications
