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 ,
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.

=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 <atyahoo<dotcom
----

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 ,
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.