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

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

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

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

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

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


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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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


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


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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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

Rick Rothstein (MVP - VB) wrote:
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