No Biff. The columns will have their rows updated weekly hence it will grow.
So if there are more than one instance of the same sequence 0s, the formula
will count them.
:
Are you saying there there will only be 1 instance of 3 consecutive 0s (if
at all)?
--
Biff
Microsoft Excel MVP
Hi, sorry but the formula doesn't seem to work as it returns #N/A result.
Based on the sample table, it should return 1 as the result was positive
[i.e. (1-0)-0.5 = +0.5].
I'll try to explain clearer using the same table:
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
Using the above table (note: A-E does not count as row), the formula cell
will check for the following conditions:
1) three (3) consecutive 0s are met in column A, then if match;
2) checks the following row (row 5 for above) if cell C5 is negative digit
(-0.5 in this case), then if match;
3) it takes (D5-E5)+C5 - i.e. in example above is ("1"-"0")+("-0.5") =
+0.5;
4) if total is + (positive) then it registers in formula cell as 1 -
which,
in the above table, applies with the result in 1.
5) IF total is -(negative) then it registers in formula cell as 0. Example
is IF cell E5 has the digit 1 then (D5-E5)+C5 will result in -0.5.
Please help me as i'm a novice with Excel and its driving me nuts...
:
What if there's multiple instances of 3 consecutive 0s? I assume that's
possible since their original request was to be able to count the number
of
instances that meet a condition.
That's how I interpret this so a single formula couldn't be used if
that's
the case.
Think we need some clarification from the OP.
--
Biff
Microsoft Excel MVP
Building on Biff's formula above (**CSE):
=LOOKUP(2,(D2
9-E2:E9+C2:C9>0)/(C2:C9<0)/
(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9,ROW(A2:A9),0))=3))
Notes:
- This returns 1,0 or #N/A depending on which conditions are met.
- If there is more than one match the result of the last match is
returned.
- A sequence ending in three 0's is not counted and will return #n/a if
no
other match is found.
:
Anyone? Please don't give up...i'm sure someone out there can help me
figure
this out...thanks a million in advance.
:
If the cells stops at the end of the consecutive 0s, then the
formula
cell
will return a 0 or nil. Is this possible?
Lets try using a simpler table:
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
Using the above table, an independent formula cell will check the
following
conditions:
1) three (3) consecutive 0s are met in column A, then if match;
2) checks the following row (row 5 for above) if cell C5 is negative
digit,
then if match;
3) it takes (D5-E5)+C5 - i.e. in example above is (1-0)-0.5 = +0.5;
4) if total is + (positive) then it registers in formula cell as 1.
If
total
is - (negative) it registers as 0 (zero).
Can SUMPRODUCT with different array formulas be used for the above?
:
For the consecutive 0s, i'd like to know
if a formula is possible to...
Good grief!!!!
My head is spinning on that one! I have no idea how to do that in
a
single.
It could be done using multiple helper formulas but there's a
problem
with
your requirement.
taking into account the cell of column C
immediately after the consecutive 0s
What if the cells are like this:
A B C D E
0 W -0.5 2 2
1 W +0.5 2 2
0 D +0.5 2 1
0 W -0.5 1 2
0 D -0.5 1 2
There is no cell immediately after the consecutive 0s.
???
--
Biff
Microsoft Excel MVP
Thanks! You're a genius.
For the consecutive 0s, i'd like to know if a formula is
possible
to
calculate the following (see table below):
A B C D E
0 W -0.5 2 2
1 W +0.5 2 2
0 D +0.5 2 1
0 W -0.5 1 2
0 D -0.5 1 2
1 W -0.5 2 1
0 W +0.5 1 1
1 D +0.5 1 2
When the condition of consecutive 0s (in this table instance is
3
in a
row)
has been met, the cell has another condition to calculate the
following:
i) taking into account the cell of column C immediately after
the
consecutive 0s (i.e. C7 in sample table above), it needs to take
(D7-E7)+C7
to equate with either a positive or negative result. Based on
above
table,
(D7-E7)+C7 is (2-1)+(-0.5) = +0.5 (positive result)
ii) another example would be if the roles are reversed for D7
and
E7 where
D7 is 1 while E7 is 2. The result would be:
(D7-E7)+C7 is (1-2)+(-0.5) = -1.5 (negative result)
With the above 2 examples, the formula cell will then register a
count (1)
if its a positive result, and zero/none (0) if its a negative
result. Is
this
possible?
Thanks again!
:
Try these array formulas** :
Count 2 consecutive Ws:
=SUM(--(FREQUENCY(IF(B2:B9="w",ROW(B2:B9)),IF(B2:B9<>"w",ROW(B2:B9)))=2))
Count 3 consecutive 0s. Assumes no empty cells within the
range.
Empty
cells
evaluate as 0 and could cause incorrect results. If there might
be
empty
cells the formula can be tweaked to account for them.
=SUM(--(FREQUENCY(IF(A2:A9=0,ROW(A2:A9)),IF(A2:A9<>0,ROW(A2:A9)))=3))
Note that these formulas are explicit in how they count.
W
W
W
W
That would not be counted as 2 instances of 2 consecutive Ws.
That
is
considered 1 instance of 4 consecutive Ws.
** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
I have the following table:
A B
0 W
1 W
0 D
0 W
0 D
1 W
0 W
1 D
Is there a way to count or sum up a series of text or number
within a
column? E.g.
i) count the number of times the alphabet W appears in
sequence
of 2
times
(i.e. B2 and B3 in a column)? Table above shows W appearing
twice in
sequence.
ii) count the number of times the number zero (0) appears in
