Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting values
If I have a column of numbers ranging from 0 to 100, what function can I use
to say in a single cell: "if any value in the column is greater than 6, return "WARNING", otherwise return nul". Thank you |
#2
|
|||
|
|||
Try: =IF(COUNTIF(A1:A100,"6"),"Warning","")
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Adam1 Chicago wrote in message ... If I have a column of numbers ranging from 0 to 100, what function can I use to say in a single cell: "if any value in the column is greater than 6, return "WARNING", otherwise return nul". Thank you |
#3
|
|||
|
|||
"Max" wrote in message
... Try: =IF(COUNTIF(A1:A100,"6"),"Warning","") That may very well be what the OP wanted ie give a warning if there are more than 6 different values. I read it differently in that I thought the OP wanted a warning when the count of the numbers of any one value was greater than 6 ie say 7 number 10's. =IF(MAX(COUNTIF(A1:A1000,ROW(INDIRECT("1:100")))) 5,"Warning","") which is an array formula - entered with Control + Shift + Enter Or I thought it was just possible then he may have meant any value was higher than 6 in which case it would be: =IF(MAX(A1:A1000)6,"Warning","") Between the three surely we have the answer somewhere <g Regards Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Max" wrote in message ... Try: =IF(COUNTIF(A1:A100,"6"),"Warning","") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Adam1 Chicago wrote in message ... If I have a column of numbers ranging from 0 to 100, what function can I use to say in a single cell: "if any value in the column is greater than 6, return "WARNING", otherwise return nul". Thank you |
#4
|
|||
|
|||
thank you, that was very helpful.
do you know if there is a way i can replce the "6" below with a cell reference? for example: =if(countif(a1:a100,"X64"),"Warning","") thanks "Max" wrote: Try: =IF(COUNTIF(A1:A100,"6"),"Warning","") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Adam1 Chicago wrote in message ... If I have a column of numbers ranging from 0 to 100, what function can I use to say in a single cell: "if any value in the column is greater than 6, return "WARNING", otherwise return nul". Thank you |
#5
|
|||
|
|||
Yes, you got it. Thank you both
"Sandy Mann" wrote: "Max" wrote in message ... Try: =IF(COUNTIF(A1:A100,"6"),"Warning","") That may very well be what the OP wanted ie give a warning if there are more than 6 different values. I read it differently in that I thought the OP wanted a warning when the count of the numbers of any one value was greater than 6 ie say 7 number 10's. =IF(MAX(COUNTIF(A1:A1000,ROW(INDIRECT("1:100")))) 5,"Warning","") which is an array formula - entered with Control + Shift + Enter Or I thought it was just possible then he may have meant any value was higher than 6 in which case it would be: =IF(MAX(A1:A1000)6,"Warning","") Between the three surely we have the answer somewhere <g Regards Sandy -- to e-mail direct replace @mailintor.com with @tiscali.co.uk "Max" wrote in message ... Try: =IF(COUNTIF(A1:A100,"6"),"Warning","") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- Adam1 Chicago wrote in message ... If I have a column of numbers ranging from 0 to 100, what function can I use to say in a single cell: "if any value in the column is greater than 6, return "WARNING", otherwise return nul". Thank you |
#6
|
|||
|
|||
do you know if there is a way i can replce the "6" below with a cell
reference? for example: =if(countif(a1:a100,"X64"),"Warning","") How about this: =if(MAX(a1:a100)X64,"Warning","") |
#7
|
|||
|
|||
do you know if there is a way i can replace=20
Adam1 Chicago wrote: the "6" below with a cell reference? =3Dif(countif(a1:a100,"X64"),"Warning","") To point to cell references, use in this way: =3DIF(COUNTIF(A1:A100,""&X64),"Warning","") where X64 contains the number -- Rgds Max xl 97 --- GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <atyahoo<dotcom ---- |
#8
|
|||
|
|||
Between the three surely=20
we have the answer somewhere <g Nothing could have survived the fusillade <g Your 1st interp on the OP and the suggestion=20 was a good one, IMHO (It wouldn't have crossed my mind ..) -- Rgds Max xl 97 --- GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <atyahoo<dotcom ---- |
#9
|
|||
|
|||
Can I add a question to this post?
How would you count if you are looking at a range of numbers, i.e. =8000 and <=9999? "Max" wrote: do you know if there is a way i can replace Adam1 Chicago wrote: the "6" below with a cell reference? =if(countif(a1:a100,"X64"),"Warning","") To point to cell references, use in this way: =IF(COUNTIF(A1:A100,""&X64),"Warning","") where X64 contains the number -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#10
|
|||
|
|||
one way:
=IF(SUMPRODUCT(--(A1:A100=8000),--(A1:A100<=9999))0,"Warning","") For an explanation of the "--" see http://www.mcgimpsey.com/excel/doubleneg.html In article , Pat Z. wrote: Can I add a question to this post? How would you count if you are looking at a range of numbers, i.e. =8000 and <=9999? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Values | Excel Worksheet Functions | |||
Counting Values | Excel Discussion (Misc queries) | |||
Counting values within a Date Range | Excel Discussion (Misc queries) | |||
Counting unique values + frequency | Excel Worksheet Functions | |||
Counting values | Excel Worksheet Functions |