Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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!


Check out our Excel Resources

Re: IF-THEN Statement for Determining if a # Lies Between 2 Other Numbers

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


Yet another way...

Posted by Mark W. on April 14, 2001 1:30 PM
...using an array formula (entered using
Control+Shift+Enter):

{=PRODUCT(B1:C1-A1)<=0}


Re: Yet another way...

Posted by Dave Hawley on April 14, 2001 8:28 PM


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


Re: Yet another way...

Posted by Mark W. on April 15, 2001 12:25 AM

> 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.


Re: Yet another way...

Posted by Dave Hawley on April 15, 2001 2:41 AM
>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


Propose that ...

Posted by Aladin Akyurek on April 15, 2001 12:11 PM
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.


Re: Propose that ...

Posted by Dave Hawley on April 15, 2001 2:52 PM
Aladin. I propose that if you too cannot take it, then don't dish it out!

OzGrid Business Applications


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.