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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"))
 
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)
 
=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"))
 
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!
 
Back
Top