Test if a number falls between 2 values

[dalymjl]

I have a number stored in Cell A1. I want to test whether or not that
number falls between 153 and 381 and to return the value TRUE or FALSE
in Cell B1. I've tried using the IF function and the AND function but
it does not like the expression "A1 between 153 and 381". Can anyone
help please?

MJD

 

[Ron Rosenfeld]

I have a number stored in Cell A1. I want to test whether or not that
number falls between 153 and 381 and to return the value TRUE or FALSE
in Cell B1. I've tried using the IF function and the AND function but
it does not like the expression "A1 between 153 and 381". Can anyone
help please?

MJD

=AND(A1>153,A1<381)

will return TRUE or FALSE given your stated conditions.
--ron

 

[Gord Dibben]

=IF(AND(A1>153),(A1<381))


Gord Dibben MS Excel MVP

 

[Dave Thomas]

Often people mean between two numbers to include the two numbers.
So if between means including the 153 and the 381 the formula becomes
=IF(AND(A1>=153),(A1<=381))

 

[Ron Rosenfeld]

=IF(AND(A1>153),(A1<381)

Interesting formula (although needs another close parentheses).

After I understood the logic, I could simplify it to:

=IF(A1>153,A1<381)

Which is one keystroke shorter than mine:

=AND(A1>153,A1<381)




--ron

 

[Dave Thomas]

Random number generators often state they generate a number between 0 and 1.
They do not mean >=0 and <= 1. The do not mean >0 and < 1. They mean >= 0
and <1. Go figure!

 

[Gord Dibben]

Ron

Don't know why I added the both IF and AND functions.

There was no logic involved, just a typo.

Should have been simply =AND(A1>153,A1<381)

But I see =IF(A1>153,A1<381) also works.

The closer got lost in the copying to the post.


Gord

 

[Dana DeLouis]

=ABS(A1-267)<114
=IF(A1>153,A1<381)
=AND(A1>153,A1<381)

Gotcha by 2 Characters :>)

 

[J@@]

Dana
Can you explain the Abs formula?
It works fine, but how does it??

Thank you
J@@ (Tahiti)

 

[Harlan Grove]

Can you explain the Abs formula?
It works fine, but how does it?? ....

....

If a < x < b, then ABS(x - midpoint) < difference/2, that is,
ABS(x-(a+b)/2)<(b-a)/2. With a = 153 and b = 381, this becomes
ABS(x-(153+381)/2)<(381-153)/2 = ABS(x-267)<114.

 

[Rick Rothstein \(MVP - VB\)]

Can you explain the Abs formula?

It works fine, but how does it??

Thank you
J@@ (Tahiti)

Harlan has given you the mathematics of the formula... let's see if a verbal
description helps any. First off, if you are not familiar with the ABS
function, it simply returns the positive value of its argument, So, ABS(5)=5
and ABS(-5)=5. Now, consider the range given = 153 to 381. The midpoint of
that range is 267 which can be found by adding one half the length of the
range to the lower range value.

Midpoint = 153 + (381 - 153) / 2 = 153 + 114 = 267

As it happens, the short cut for this calculation is to add the endpoints of
the range together and divide by 2

Midpoint = (153 + 381) / 2 = 534 / 2 = 267

which is what Harlan used. Then math behind that is quite simple. Starting
with my original method of calculating the midpoint (and using A and B for
the endpoints of the range)...

Midpoint = A + (B - A) / 2 = A + B/2 - A/2 = A/2 + B/2 = (A + B) / 2

Anyway, the main point to see in this is that all values in the range must
lie within half the length of the range from the midpoint. If you don't see
that at first, think about it... it is sort of a definition. So, if a value
V is to be in the range, then it must be closer to the midpoint than an end
point is. Said another way, the positive difference between V and the
midpoint must be less than half the length of the range. For the given
range, the positive difference of V and the Midpoint is found by ABS(V-267)
and it must be less than half the length of the range which is
(381-153)/2... note that is the second math expression in my original
Midpoint calculation above... which is 114 after completing the math. Okay,
now put it together.. for the value V to be in the range, this expression
must hold...

ABS(V - 267) < 114

For the spreadsheet... A1 is the value V. One final note. If we use just the
"less than" symbol (<), the endpoints are not part of the range. If we use
the "less than or equal" symbol (<=), then the endpoints are part of the
range.

In looking back at what I wrote, I don't think it came out as clear as how I
see it in my head; but perhaps you will find it useful nonetheless.

Rick

 

[Sandy Mann]

Rick,

I assume that this is an internal rounding error, (it always is rounding
errors in XL <g>) but why would:

=ABS(153-AVERAGE(153,381))<144

return FALSE (in XL97)

when:

=ABS(153-AVERAGE(153,381))<144

Returns TRUE?

As I said I assumed that there was dross attached to the value returned by
AVERAGE() but even:

=ABS(153-INT(AVERAGE(153,381)))<144

Returns TRUE?

--
Regards,

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

 

[Rick Rothstein \(MVP - VB\)]

Hmm! That is interesting... and I don't understand why it should report
TRUE. The sum of 153 and 381 is 534, an even number. One-half of that is
exactly 267... there should be no floating point issues arising from that
calculation that I can see.

Rick

 

[J@@]

Thank you Harlan & Rick
I was far away from it.
J@@