ExcelBanter - In between

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - In between (https://www.excelbanter.com/excel-worksheet-functions/19327-between.html)

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(M42M46,M42M50),1,
IF(AND(M42<M46,M42<M50),1,
IF(AND(M42M52,M42M53),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...

Somewhat shorter:

=IF((M42<M46)+(M42M50)+(M42<M52)+(M42M53)1,1)

HTH
Jason
Atlanta, GA

-----Original Message-----
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(M42M46,M42M50),1,
IF(AND(M42<M46,M42<M50),1,
IF(AND(M42M52,M42M53),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...

.