number of cells

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

Guest

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
 
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(a1>0;1;0)
Copie this from B1 to B50

Then count (B1:B50). (Zigma)

Maybe....


Just Try it ................

:cool:
 
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
 
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
 
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
 
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
 
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
 
actually, all 4 of them :-)

KL


Darius said:
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
Click to expand...
 
Back
Top