Is there a BETWEEN function?

[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

 

[Pete_UK]

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

 

[Arlen]

Makes sense. Thanks for thinking of it.

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

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[T. Valko]

Another one:

=A1=MEDIAN(A1:C1)

 

[Sandy Mann]

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

 

[Sandy Mann]

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

 

[Arlen]

Thanks, Pete, Biff and Sandy

It's good to have several tricks up your arsenal.

Arlen