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

 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.

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

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

## Re: Yet another way...

Posted by Dave Hawley on April 15, 2001 2:41 AM

Pointing out Aladins mistakes wont shift the focus, you stuffed up.

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

## 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!