how write excel formula numbers in column => &=< between 20-40

How do I write formula for numbers in a column and I want the numbers that
equal to and greater than AND equal to and less than. Ex. all numbers in a
column between 20 and 40. Thanks for any help.

Ron,

Assuming your number to test is in cell A1, add this formula to B1 or some
other cell:

=IF(A1>=20,IF(A1<=40,"In Range","Out of Range"))

----
Regards,
John Mansfield
http://www.pdbook.com


"ron" wrote:

> How do I write formula for numbers in a column and I want the numbers that
> equal to and greater than AND equal to and less than. Ex. all numbers in a
> column between 20 and 40. Thanks for any help.

Not sure what you mean by "a formula for numbers"...

Do you want to generate them (assuming you mean integers)?

A1: =20
A2: =A1+1

copy down to A21.

Do you want to place them in another column?

Assuming numbers in column A:

B1: =IF(AND(A1>=20,A1<=40),A1,"")

copy down as far as necessary.

Do you want to count them?

B1: =COUNTIF(A:A,">=20") - COUNTIF(A:A,">40")

or

B1: =SUMPRODUCT(--(A1:A1000>=20),--(A1:A1000<=40))

Do you want to sum them?

B1: =SUMIF(A:A,">=20") - SUMIF(A:A,">40")

or

B1: =SUMPRODUCT(--(A1:A1000>=20),--(A1:A1000<=40),A1:A1000)


In article <[email protected]>,
ron <[email protected]> wrote:

> How do I write formula for numbers in a column and I want the numbers that
> equal to and greater than AND equal to and less than. Ex. all numbers in a
> column between 20 and 40. Thanks for any help.

> =IF(A1>=20,IF(A1<=40,"In Range","Out of Range"))

Just to add on a little to John's suggestion ..
think you could also try something like:
=IF(A1="","",IF(AND(A1>=20,A1<=40),"In Range","Out of Range"))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Thanks to all who contributed to my question. I learned two things: need to
write my question better and the answer to my question.
Je your option to countif was exactly what I was looking for and thank you
very much. Hate to say how may hours and times I tried to get the formula.
Hate to admit it but I was not even close!
--
Ron


"JE McGimpsey" wrote:

> Not sure what you mean by "a formula for numbers"...
>
> Do you want to generate them (assuming you mean integers)?
>
> A1: =20
> A2: =A1+1
>
> copy down to A21.
>
> Do you want to place them in another column?
>
> Assuming numbers in column A:
>
> B1: =IF(AND(A1>=20,A1<=40),A1,"")
>
> copy down as far as necessary.
>
> Do you want to count them?
>
> B1: =COUNTIF(A:A,">=20") - COUNTIF(A:A,">40")
>
> or
>
> B1: =SUMPRODUCT(--(A1:A1000>=20),--(A1:A1000<=40))
>
> Do you want to sum them?
>
> B1: =SUMIF(A:A,">=20") - SUMIF(A:A,">40")
>
> or
>
> B1: =SUMPRODUCT(--(A1:A1000>=20),--(A1:A1000<=40),A1:A1000)
>
>
> In article <[email protected]>,
> ron <[email protected]> wrote:
>
> > How do I write formula for numbers in a column and I want the numbers that
> > equal to and greater than AND equal to and less than. Ex. all numbers in a
> > column between 20 and 40. Thanks for any help.
>