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

On Sat, 21 Jul 2007 17:51:22 +0100, dalymjl
wrote:


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(A1153,A1<381)

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

=IF(AND(A1153),(A1<381))


Gord Dibben MS Excel MVP

On Sat, 21 Jul 2007 17:51:22 +0100, dalymjl
wrote:


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

On Sat, 21 Jul 2007 10:47:25 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

=IF(AND(A1153),(A1<381)

Interesting formula (although needs another close parentheses).

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

=IF(A1153,A1<381)

Which is one keystroke shorter than mine:

=AND(A1153,A1<381)




--ron

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(A1153,A1<381)

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

The closer got lost in the copying to the post.


Gord

On Sat, 21 Jul 2007 16:09:48 -0400, Ron Rosenfeld
wrote:

On Sat, 21 Jul 2007 10:47:25 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

=IF(AND(A1153),(A1<381)

Interesting formula (although needs another close parentheses).

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

=IF(A1153,A1<381)

Which is one keystroke shorter than mine:

=AND(A1153,A1<381)




--ron

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

Gotcha by 2 Characters :)

--
Dana DeLouis


"Ron Rosenfeld" wrote in message
...
On Sat, 21 Jul 2007 10:47:25 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

=IF(AND(A1153),(A1<381)

Interesting formula (although needs another close parentheses).

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

=IF(A1153,A1<381)

Which is one keystroke shorter than mine:

=AND(A1153,A1<381)
--ron

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

Thank you
J@@ (Tahiti)

Dana DeLouis wrote:
=ABS(A1-267)<114
=IF(A1153,A1<381)
=AND(A1153,A1<381)

Gotcha by 2 Characters :)

"J@@" wrote...
Can you explain the Abs formula?
It works fine, but how does it??
....
Dana DeLouis wrote:
=ABS(A1-267)<114
....

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.

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

Thank you
J@@ (Tahiti)

Dana DeLouis wrote:
=ABS(A1-267)<114
=IF(A1153,A1<381)
=AND(A1153,A1<381)

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

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))


"dalymjl" wrote in message
...

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




--
dalymjl

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!

"dalymjl" wrote in message
...

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




--
dalymjl