Between

Posted by Scott on January 19, 2001 4:40 PM

I am trying to create a single cell formula that will determine if a particular cell is between a certain high and low value. How is the best way to go about this?

Posted by Aladin Akyurek on January 19, 2001 6:45 PM

Supposing that the value to evaluate is in A1, the high value in C1, and the low value in D1. Then

B1 =if(and(A1>=$C$1,A1 LessThanOrEqualTo $D$1),1,0)

or, alternatively,

B1 =(A1>=$C$1)*(A1LessThanOrEqualTo $D$1)

where 1 as result means TRUE and 0 as result means FALSE.

You may also name C1 "Low" and D1 "High" via the Name Box and use these names in one of the above formulas you prefer.

Note. Replace "LessThanOrEqualTo" by the appropriate strring of symbols.

I hope this is what you asked for.

Aladin