Part 2 - Counting series of text/number sequence

  • Thread starter Thread starter andrew
  • Start date Start date
A

andrew

This is in relation to my other post (same topic). Using the same table below:

A B C D E
1 W -0.5 2 2
0 W +0.5 2 2
0 D +0.5 2 1
0 W -0.5 1 2
1 D -0.5 1 0

I'm trying to get the formula to just count the number of occurences based
on the following conditions:
1) three (3) consecutive 0s are met in column A, then if match;
2) checks the subsequent row (row 5 for above) if cell C5 is a negative digit
(-0.5 in this case), it will just count it as 1. If its a positive digit
then 0.

The rows are updated on weekly basis, hence it has to cater for the
possibility of the sequence happening more than once. The formula cell will
just sum up the number of times the sequence occurs (where column C is
relative).

The other post had additional conditions but thanks to Lori and Biff, the
working formula was:

=SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9<>0,ROW(A1:A9),0))=3)
*(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C9>0)))

Where do i modify the above to suit the conditions set forth in this post?
 
Anyone?

I've played around with the formula but to no avail. Any help would be
appreciated, thanks in advance.
 
Help??

I tried a few ways to edit the below formula based on my original post, but
kept getting errors:

=SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9<>0,ROW(A1:A9),0))=3)*(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C9>0)))

Still trying to figure out this (1-FREQUENCY(2,--A1:A9)) too...
 
HELP?

andrew said:
Help??

I tried a few ways to edit the below formula based on my original post, but
kept getting errors:

=SUM(IF((FREQUENCY(IF(A1:A9=0,ROW(A1:A9)),IF(A1:A9<>0,ROW(A1:A9),0))=3)*(1-FREQUENCY(2,--A1:A9)),(C1:C9<0)*(D1:D9-E1:E9+C1:C9>0)))

Still trying to figure out this (1-FREQUENCY(2,--A1:A9)) too...
Click to expand...
 
Back
Top