In between

J

J Shrimps, Jr.

Is there no "between" function like Access in Excel?
Need to determine if a value is between two sets of
values.
Formula below is what I'm using
from Walkenbach's book, displaying
the number 1 if the value in cell M42 does
not fall within values in cells M46, M50
M52 and M53.
=IF(AND(M42>M46,M42>M50),1,
IF(AND(M42<M46,M42<M50),1,
IF(AND(M42>M52,M42>M53),1,
IF(AND(M42<M52,M42<M53),1))))
Seems a bit long just to check if a value
falls between two other sets of values...
 
J

Jason Morin

Somewhat shorter:

=IF((M42<M46)+(M42>M50)+(M42<M52)+(M42>M53)>1,1)

HTH
Jason
Atlanta, GA
 
Top