Is there a BETWEEN function?

  • Thread starter Thread starter Arlen
  • Start date Start date
A

Arlen

How do you test if a number is equal to or BETWEEN two other numbers, rather
than greater than or equal to the smallest and less than or equal to the
biggest.

Reason being, the smaller number and the larger number may swap cell
addresses at times.

Thanks, all.

Arlen
 
You can use MIN to get the smaller of the two, and MAX for the larger,
so you would have something like:

=IF(AND(A1>=MIN(B1,C1),A1<=MAX(B1,C1)),true_action,false_action)

where B1 and C1 specify the range and either could be the larger, and
A1 is the value you are testing.

Hope this helps.

Pete
 
Makes sense. Thanks for thinking of it.

Arlen

Pete_UK said:
You can use MIN to get the smaller of the two, and MAX for the larger,
so you would have something like:

=IF(AND(A1>=MIN(B1,C1),A1<=MAX(B1,C1)),true_action,false_action)

where B1 and C1 specify the range and either could be the larger, and
A1 is the value you are testing.

Hope this helps.

Pete
Click to expand...
 
Too late because I'm not a night owl like Pete but just for fun, with the
test number in A1 and the limits in B1 & C1:

=IF(MEDIAN(B1,C1,A1)=A1,"Between or Equal","Not Between")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Never saw your post until after I posted Biff - better still.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top