Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
=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 ---- |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
In excel, column headings displaying as numbers | Excel Discussion (Misc queries) | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Excel column headings from numbers to letters | Excel Discussion (Misc queries) | |||
EXCEL FORMULA | Excel Worksheet Functions |