A
andrew
Firstly, i'd like to thank everyone who has helped me in solving the various
queries posed. You guys are simply the best!
Please bear with me while i try to explain in detail. I'm currently working
on a project which consist of 10 worksheets (sandwiched between Start and End
sheets) and a Summary sheet at the beginning. All 10 sheets are formatted the
same, all consisting of the following table BUT updated on a weekly basis
(i.e. rows keep increasing until week 52).
A B C D
Y -0.5 0 2
W 0.5 0 2
W 0.5 1 1
X -1 2 1
Y 0.5 1 2
Y 0.5 0 1
W -0.5 1 0
W 0.5 1 0
X 0 0 0
X 0 0 0
W -0.5 2 1
Y -0.5 1 2
W 0.5 2 1
W 0.5 2 2
Y 0.5 0 2
Y 0.5 0 1
Using the above table (note: A-D does not count as row), the formula cell
will check for the following conditions:
1) SEQUENCE OF TWO (2) ZEROS (WITH POSITIVE RESULT)
a) two (2) consecutive 0s are met in column C, then if match;
b) checks the following row (row 12 for above) if cell B12 is negative digit
(-0.5 in this case), then if match;
c) it takes (C12-D12)+B12 ,i.e. in example above is (2-1)+(-0.5) = +0.5;
d) if value returned is positive (as above) then it counts as 1 occurence.
As the row keeps adding, and whenever conditions (a) to (c) are met, the
formula cell will keep counting to the total times it returned a positive
value.
Currently i have this formula but it doesn't seem to work at times
(returning #N/A or missing some counts on some of the worksheets):
=SUM(IF((FREQUENCY(IF(T7:T45=0,ROW(T7:T45)),IF(T7:T45<>0,ROW(T7:T45),0))=2)*(1-FREQUENCY(2,--T7:T45)),(S7:S45<0)*(T7:T45-V7:V45+S7:S45>0)))
2. SEQUENCE OF TWO ZEROES (TOTAL)
a) two (2) consecutive 0s are met in column C, then if match;
b) checks the following row (row 12 for above) if cell B12 is negative digit
(-0.5 in this case), then if match;
c) count the occurence of the sequence as 1. As the rows keep adding and
conditions (a) and (b) are met, it will just add to the total count.
queries posed. You guys are simply the best!
Please bear with me while i try to explain in detail. I'm currently working
on a project which consist of 10 worksheets (sandwiched between Start and End
sheets) and a Summary sheet at the beginning. All 10 sheets are formatted the
same, all consisting of the following table BUT updated on a weekly basis
(i.e. rows keep increasing until week 52).
A B C D
Y -0.5 0 2
W 0.5 0 2
W 0.5 1 1
X -1 2 1
Y 0.5 1 2
Y 0.5 0 1
W -0.5 1 0
W 0.5 1 0
X 0 0 0
X 0 0 0
W -0.5 2 1
Y -0.5 1 2
W 0.5 2 1
W 0.5 2 2
Y 0.5 0 2
Y 0.5 0 1
Using the above table (note: A-D does not count as row), the formula cell
will check for the following conditions:
1) SEQUENCE OF TWO (2) ZEROS (WITH POSITIVE RESULT)
a) two (2) consecutive 0s are met in column C, then if match;
b) checks the following row (row 12 for above) if cell B12 is negative digit
(-0.5 in this case), then if match;
c) it takes (C12-D12)+B12 ,i.e. in example above is (2-1)+(-0.5) = +0.5;
d) if value returned is positive (as above) then it counts as 1 occurence.
As the row keeps adding, and whenever conditions (a) to (c) are met, the
formula cell will keep counting to the total times it returned a positive
value.
Currently i have this formula but it doesn't seem to work at times
(returning #N/A or missing some counts on some of the worksheets):
=SUM(IF((FREQUENCY(IF(T7:T45=0,ROW(T7:T45)),IF(T7:T45<>0,ROW(T7:T45),0))=2)*(1-FREQUENCY(2,--T7:T45)),(S7:S45<0)*(T7:T45-V7:V45+S7:S45>0)))
2. SEQUENCE OF TWO ZEROES (TOTAL)
a) two (2) consecutive 0s are met in column C, then if match;
b) checks the following row (row 12 for above) if cell B12 is negative digit
(-0.5 in this case), then if match;
c) count the occurence of the sequence as 1. As the rows keep adding and
conditions (a) and (b) are met, it will just add to the total count.