Is there a BETWEEN function?
 

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

Is there a BETWEEN function?
 

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

On Jul 24, 12:35*am, Arlen wrote:
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

Is there a BETWEEN function?
 

Makes sense. Thanks for thinking of it.

Arlen

"Pete_UK" wrote:

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

On Jul 24, 12:35 am, Arlen wrote:
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

Is there a BETWEEN function?
 

You're welcome - thanks for feeding back.

Pete

On Jul 24, 1:08*am, Arlen wrote:
Makes sense. *Thanks for thinking of it.

Arlen

Is there a BETWEEN function?
 

Another one:

=A1=MEDIAN(A1:C1)

--
Biff
Microsoft Excel MVP


"Arlen" wrote in message
...
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

Is there a BETWEEN function?
 

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


Replace @mailinator.com with @tiscali.co.uk


"Arlen" wrote in message
...
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

Is there a BETWEEN function?
 

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


Replace @mailinator.com with @tiscali.co.uk


"T. Valko" wrote in message
...
Another one:

=A1=MEDIAN(A1:C1)

--
Biff
Microsoft Excel MVP


"Arlen" wrote in message
...
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

Is there a BETWEEN function?
 

Thanks, Pete, Biff and Sandy

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

Arlen


"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Arlen" wrote in message
...
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