Home |
Search |
Today's Posts |
#1
|
|||
|
|||
number of cells
Hi there;
1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
#3
|
|||
|
|||
Hi Darius, I'm a Newbie and Dummy here, but maybe this is (can be) a solution. Lets say your numbers are in A1 - A50. Use Colom B. B1 = if(a10;1;0) Copie this from B1 to B50 Then count (B1:B50). (Zigma) Maybe.... Just Try it ................ -- skrol ------------------------------------------------------------------------ skrol's Profile: http://www.excelforum.com/member.php...o&userid=27126 View this thread: http://www.excelforum.com/showthread...hreadid=466520 |
#4
|
|||
|
|||
I wish I was that much young, no it is not homework,
Skrol I am afraid you way is not that much efficient. hope I get some other response Best Darius "Darius" wrote: Hi there; 1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
#5
|
|||
|
|||
Hi Darius,
For the first task use the following formula: =COUNTIF(A1:A10,"0") Regards, KL "Darius" wrote in message ... Hi there; 1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
#6
|
|||
|
|||
Hi again,
For task 2 try the following: =SUMPRODUCT(--(COUNTIF(OFFSET(A1:A10,(ROW(INDIRECT("1:"&ROWS(A1: A10)/3))-1)*3,,3),"0")=3)) Reagards, KL "KL" wrote in message ... Hi Darius, For the first task use the following formula: =COUNTIF(A1:A10,"0") Regards, KL "Darius" wrote in message ... Hi there; 1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
#7
|
|||
|
|||
thanks but the second equation (below)which I use for "E3:E94" and contains
numbers, results in: #REF Any suggestion? =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3: E94)/3))-1)*3,,3),"0")=3)) Best Darius "KL" wrote: Hi Darius, For the first task use the following formula: =COUNTIF(A1:A10,"0") Regards, KL "Darius" wrote in message ... Hi there; 1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
#8
|
|||
|
|||
I assumed the number of cells in your range would be a multiple of 3 :-( Try
this: =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS (E3:E94)/3)))-1)*3,,3),"0")=3)) KL "Darius" wrote in message ... thanks but the second equation (below)which I use for "E3:E94" and contains numbers, results in: #REF Any suggestion? =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3: E94)/3))-1)*3,,3),"0")=3)) Best Darius "KL" wrote: Hi Darius, For the first task use the following formula: =COUNTIF(A1:A10,"0") Regards, KL "Darius" wrote in message ... Hi there; 1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
#9
|
|||
|
|||
Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells,
which of those "3" should be replaced by 5? Thanks a lot. Besr Darius "KL" wrote: I assumed the number of cells in your range would be a multiple of 3 :-( Try this: =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS (E3:E94)/3)))-1)*3,,3),"0")=3)) KL "Darius" wrote in message ... thanks but the second equation (below)which I use for "E3:E94" and contains numbers, results in: #REF Any suggestion? =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3: E94)/3))-1)*3,,3),"0")=3)) Best Darius "KL" wrote: Hi Darius, For the first task use the following formula: =COUNTIF(A1:A10,"0") Regards, KL "Darius" wrote in message ... Hi there; 1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
#10
|
|||
|
|||
actually, all 4 of them :-)
KL "Darius" wrote in message ... Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells, which of those "3" should be replaced by 5? Thanks a lot. Besr Darius "KL" wrote: I assumed the number of cells in your range would be a multiple of 3 :-( Try this: =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS (E3:E94)/3)))-1)*3,,3),"0")=3)) KL "Darius" wrote in message ... thanks but the second equation (below)which I use for "E3:E94" and contains numbers, results in: #REF Any suggestion? =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3: E94)/3))-1)*3,,3),"0")=3)) Best Darius "KL" wrote: Hi Darius, For the first task use the following formula: =COUNTIF(A1:A10,"0") Regards, KL "Darius" wrote in message ... Hi there; 1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
#11
|
|||
|
|||
Thank you so much, works excellent
Darius "KL" wrote: actually, all 4 of them :-) KL "Darius" wrote in message ... Thanks, works fine, if instead of 3 consequtive cells I need for 5 cells, which of those "3" should be replaced by 5? Thanks a lot. Besr Darius "KL" wrote: I assumed the number of cells in your range would be a multiple of 3 :-( Try this: =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&INT(ROWS (E3:E94)/3)))-1)*3,,3),"0")=3)) KL "Darius" wrote in message ... thanks but the second equation (below)which I use for "E3:E94" and contains numbers, results in: #REF Any suggestion? =SUMPRODUCT(--(COUNTIF(OFFSET(E3:E94,(ROW(INDIRECT("1:"&ROWS(E3: E94)/3))-1)*3,,3),"0")=3)) Best Darius "KL" wrote: Hi Darius, For the first task use the following formula: =COUNTIF(A1:A10,"0") Regards, KL "Darius" wrote in message ... Hi there; 1-What is the equation to find the number of cells in a range which their values are more than 0.0? 2- How to count the number of cells in range which for every 3 consqueative cells are non 0.0, I mean if cells 1, 2, and 3 are more than 0.0, then count = 1 then next 3 cells, ... and if one 3 consequative cells have (even one cell) a 0.0 value then that 3 cells donot add to our count number? Appreciate any help. Best Darius |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked cells show wrong number | Links and Linking in Excel | |||
Find all cells with a number and mulitply | Excel Discussion (Misc queries) | |||
Help adding text values | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
only include cells beginning with specified character or number | Excel Worksheet Functions |