And then there were ZEROs...

[andrew]

HELP, i'm going bonkers with zeroes. I've actually posted this quite a while
back, and worked on various suggested formulas but to no avail. I hope you
can be patient as i will have to explain quite elaborately due to the nature
of the required results. Here's what been bugging me (its for a football
statistics):

A B C
-0.5 1 0
+0.5 0 0
+0.5 0 1
+0.5 1 1
-0.5 0 0
-0.5 1 0
+0.5 0 2
-0.5 0 1
-0.5 0 3
+0.5 1 1

I would like to count the following:
a) two (2) consecutive 0s are met in column B, with condition that the
subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this
case). If YES, count the sequence everytime it happens within the column (if
NO, don't count).
=FORMULA?

b) While (a) just count the number of times it occurs, another formula is
required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then
count how many times such an event happens within the column.


For both the above, it must be noted that in the event another 0 appears
after the two (2) consecutive 0s (see B7:B9), the formula will still remain
and count the subsequent row based on the conditions provided in (a) and (b).
Is this possible?

HELP! Thanks!

 

[T. Valko]

its for a football statistics

Maybe if you explained what this data actually means we might be able to
come up with something that's workable.

If you recall, I replied to a couple of your previous posts and I had my
doubts if what you're asking for is even possible. It looks like my doubts
have proven to be accurate! <g> Lori also replied and between us we couldn't
come up with anything that worked. Lori is a top-notch formula person! I'm
not a slouch, either!

 

[Lars-Åke Aspelin]

HELP, i'm going bonkers with zeroes. I've actually posted this quite a while
back, and worked on various suggested formulas but to no avail. I hope you
can be patient as i will have to explain quite elaborately due to the nature
of the required results. Here's what been bugging me (its for a football
statistics):

A B C
-0.5 1 0
+0.5 0 0
+0.5 0 1
+0.5 1 1
-0.5 0 0
-0.5 1 0
+0.5 0 2
-0.5 0 1
-0.5 0 3
+0.5 1 1

I would like to count the following:
a) two (2) consecutive 0s are met in column B, with condition that the
subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5 in this
case). If YES, count the sequence everytime it happens within the column (if
NO, don't count).
=FORMULA?

b) While (a) just count the number of times it occurs, another formula is
required to count if condition (a) is met, AND (B4-C4)+A4=+ve number then
count how many times such an event happens within the column.


For both the above, it must be noted that in the event another 0 appears
after the two (2) consecutive 0s (see B7:B9), the formula will still remain
and count the subsequent row based on the conditions provided in (a) and (b).
Is this possible?

HELP! Thanks!

I don't understand b). Please explain.
What is "+ve number" ?

I don't understand what you mean by "the formula will still remain and
count the subsequent row..." in the note. Please explain.

I am not sure that I have understood a) correctly, but anyway

Try this formula for a):
Note: This is an array formula that has to be entered by
CTRL+SHIFT+ENTER rather than just ENTER.

=SUMPRODUCT(- -(B$1:B$10=0),- -(B$2:B$11=0),- -(- -A$3:A$12>0))

This gives the result 2 for the test data you have provided

Hope this helps / Lars-Åke

 

[andrew]

Hi Biff, firstly thanks for being patient and helping me out with your
various formulas. You've been great! Lori too, and she was close the last
time...

Anyway, let me see if i can explain clearer. What i intend to do from the
table is to analyse for sequences of zeros. What the table shows are the
results of matches played by a team. The +0.5 or -0.5 are just handicaps that
the team provides to its opponent. The results are updated weekly in a column
from the fixtures played over the year. Now, here's the actual part of
analysis which i'm trying to derive with formulas:

1) To check for sequences of two (2) zeroes on column B (score of home team)
with the conditions provided in (a) per original post. As for (b), it is just
a total count of the occurence regardless if the result [(column B minus
column C) + column A] is a positive value or not.

So, as an example, if i were to refer to the original table posted, B2 and
B3 had 0s (i.e. the team did not score consecutively in 2 matches). On B4
(subsequent row), the team played to a 1-1 draw, with a handicap of +0.5.
From this example, the formula will count this as 1 instance as it returned a
positive value ,i.e.
(1-1)+0.5 = +0.5
IF the result had been 1-2 for row B4 and C4, then it return a negative
value, i.e.
(1-2)+0.5 = -0.5 (which the formula will not count as 1 instance)


If the above is possible, then i'm actually looking at a table that stores
the following information:
i) sequence of two 0s, with +0.5 handicap (another column will store the
total count of such occurence regardless if the subsequent row returned a
positive or negative value for row (B-C)+A)
ii) sequence of two 0s, giving -0.5 handicap

Does the above make sense? If not, i can e-mail you a sample sheet with the
actual data and formulas....Thanks in advance!

 

[T. Valko]

i can e-mail you a sample sheet with the actual data and formulas

OK, let's see what we're dealing with! I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP

Hi Biff, firstly thanks for being patient and helping me out with your
various formulas. You've been great! Lori too, and she was close the last
time...

Anyway, let me see if i can explain clearer. What i intend to do from the
table is to analyse for sequences of zeros. What the table shows are the
results of matches played by a team. The +0.5 or -0.5 are just handicaps
that
the team provides to its opponent. The results are updated weekly in a
column
from the fixtures played over the year. Now, here's the actual part of
analysis which i'm trying to derive with formulas:

1) To check for sequences of two (2) zeroes on column B (score of home
team)
with the conditions provided in (a) per original post. As for (b), it is
just
a total count of the occurence regardless if the result [(column B minus
column C) + column A] is a positive value or not.

So, as an example, if i were to refer to the original table posted, B2 and
B3 had 0s (i.e. the team did not score consecutively in 2 matches). On B4
(subsequent row), the team played to a 1-1 draw, with a handicap of +0.5.
From this example, the formula will count this as 1 instance as it
returned a
positive value ,i.e.
(1-1)+0.5 = +0.5
IF the result had been 1-2 for row B4 and C4, then it return a negative
value, i.e.
(1-2)+0.5 = -0.5 (which the formula will not count as 1 instance)


If the above is possible, then i'm actually looking at a table that stores
the following information:
i) sequence of two 0s, with +0.5 handicap (another column will store the
total count of such occurence regardless if the subsequent row returned a
positive or negative value for row (B-C)+A)
ii) sequence of two 0s, giving -0.5 handicap

Does the above make sense? If not, i can e-mail you a sample sheet with
the
actual data and formulas....Thanks in advance!

Maybe if you explained what this data actually means we might be able to
come up with something that's workable.

If you recall, I replied to a couple of your previous posts and I had my
doubts if what you're asking for is even possible. It looks like my
doubts
have proven to be accurate! <g> Lori also replied and between us we
couldn't
come up with anything that worked. Lori is a top-notch formula person!
I'm
not a slouch, either!

 

[andrew]

Err, i don't see any e-mail addy below except in your profile (which i tried
to send but mail bounced..). Can you send a test e-mail to me so that i can
reply with the data for your review? Thanks!

i can e-mail you a sample sheet with the actual data and formulas

OK, let's see what we're dealing with! I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP

Hi Biff, firstly thanks for being patient and helping me out with your
various formulas. You've been great! Lori too, and she was close the last
time...

Anyway, let me see if i can explain clearer. What i intend to do from the
table is to analyse for sequences of zeros. What the table shows are the
results of matches played by a team. The +0.5 or -0.5 are just handicaps
that
the team provides to its opponent. The results are updated weekly in a
column
from the fixtures played over the year. Now, here's the actual part of
analysis which i'm trying to derive with formulas:

1) To check for sequences of two (2) zeroes on column B (score of home
team)
with the conditions provided in (a) per original post. As for (b), it is
just
a total count of the occurence regardless if the result [(column B minus
column C) + column A] is a positive value or not.

So, as an example, if i were to refer to the original table posted, B2 and
B3 had 0s (i.e. the team did not score consecutively in 2 matches). On B4
(subsequent row), the team played to a 1-1 draw, with a handicap of +0.5.
From this example, the formula will count this as 1 instance as it
returned a
positive value ,i.e.
(1-1)+0.5 = +0.5
IF the result had been 1-2 for row B4 and C4, then it return a negative
value, i.e.
(1-2)+0.5 = -0.5 (which the formula will not count as 1 instance)


If the above is possible, then i'm actually looking at a table that stores
the following information:
i) sequence of two 0s, with +0.5 handicap (another column will store the
total count of such occurence regardless if the subsequent row returned a
positive or negative value for row (B-C)+A)
ii) sequence of two 0s, giving -0.5 handicap

Does the above make sense? If not, i can e-mail you a sample sheet with
the
actual data and formulas....Thanks in advance!

its for a football statistics

Maybe if you explained what this data actually means we might be able to
come up with something that's workable.

If you recall, I replied to a couple of your previous posts and I had my
doubts if what you're asking for is even possible. It looks like my
doubts
have proven to be accurate! <g> Lori also replied and between us we
couldn't
come up with anything that worked. Lori is a top-notch formula person!
I'm
not a slouch, either!


--
Biff
Microsoft Excel MVP


HELP, i'm going bonkers with zeroes. I've actually posted this quite a
while
back, and worked on various suggested formulas but to no avail. I hope
you
can be patient as i will have to explain quite elaborately due to the
nature
of the required results. Here's what been bugging me (its for a
football
statistics):

A B C
-0.5 1 0
+0.5 0 0
+0.5 0 1
+0.5 1 1
-0.5 0 0
-0.5 1 0
+0.5 0 2
-0.5 0 1
-0.5 0 3
+0.5 1 1

I would like to count the following:
a) two (2) consecutive 0s are met in column B, with condition that the
subsequent row (row 4 for above) if cell A4 is a positive digit (+0.5
in
this
case). If YES, count the sequence everytime it happens within the
column
(if
NO, don't count).
=FORMULA?

b) While (a) just count the number of times it occurs, another formula
is
required to count if condition (a) is met, AND (B4-C4)+A4=+ve number
then
count how many times such an event happens within the column.


For both the above, it must be noted that in the event another 0
appears
after the two (2) consecutive 0s (see B7:B9), the formula will still
remain
and count the subsequent row based on the conditions provided in (a)
and
(b).
Is this possible?

HELP! Thanks!

 

[T. Valko]

i don't see any e-mail addy below

No, it's there. It's just disguised to keep the spam trawlers from
recognizing it.

Here it is again:

xl can help at comcast period net

Rmove the word "can". Replace "at" and "period" with their respective
symbols and remove all the spaces.

--
Biff
Microsoft Excel MVP

Err, i don't see any e-mail addy below except in your profile (which i
tried
to send but mail bounced..). Can you send a test e-mail to me so that i
can
reply with the data for your review? Thanks!

i can e-mail you a sample sheet with the actual data and formulas

OK, let's see what we're dealing with! I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP

Hi Biff, firstly thanks for being patient and helping me out with your
various formulas. You've been great! Lori too, and she was close the
last
time...

Anyway, let me see if i can explain clearer. What i intend to do from
the
table is to analyse for sequences of zeros. What the table shows are
the
results of matches played by a team. The +0.5 or -0.5 are just
handicaps
that
the team provides to its opponent. The results are updated weekly in a
column
from the fixtures played over the year. Now, here's the actual part of
analysis which i'm trying to derive with formulas:

1) To check for sequences of two (2) zeroes on column B (score of home
team)
with the conditions provided in (a) per original post. As for (b), it
is
just
a total count of the occurence regardless if the result [(column B
minus
column C) + column A] is a positive value or not.

So, as an example, if i were to refer to the original table posted, B2
and
B3 had 0s (i.e. the team did not score consecutively in 2 matches). On
B4
(subsequent row), the team played to a 1-1 draw, with a handicap of
+0.5.
From this example, the formula will count this as 1 instance as it
returned a
positive value ,i.e.
(1-1)+0.5 = +0.5
IF the result had been 1-2 for row B4 and C4, then it return a negative
value, i.e.
(1-2)+0.5 = -0.5 (which the formula will not count as 1 instance)


If the above is possible, then i'm actually looking at a table that
stores
the following information:
i) sequence of two 0s, with +0.5 handicap (another column will store
the
total count of such occurence regardless if the subsequent row returned
a
positive or negative value for row (B-C)+A)
ii) sequence of two 0s, giving -0.5 handicap

Does the above make sense? If not, i can e-mail you a sample sheet with
the
actual data and formulas....Thanks in advance!


:

its for a football statistics

Maybe if you explained what this data actually means we might be able
to
come up with something that's workable.

If you recall, I replied to a couple of your previous posts and I had
my
doubts if what you're asking for is even possible. It looks like my
doubts
have proven to be accurate! <g> Lori also replied and between us we
couldn't
come up with anything that worked. Lori is a top-notch formula person!
I'm
not a slouch, either!


--
Biff
Microsoft Excel MVP


HELP, i'm going bonkers with zeroes. I've actually posted this quite
a
while
back, and worked on various suggested formulas but to no avail. I
hope
you
can be patient as i will have to explain quite elaborately due to
the
nature
of the required results. Here's what been bugging me (its for a
football
statistics):

A B C
-0.5 1 0
+0.5 0 0
+0.5 0 1
+0.5 1 1
-0.5 0 0
-0.5 1 0
+0.5 0 2
-0.5 0 1
-0.5 0 3
+0.5 1 1

I would like to count the following:
a) two (2) consecutive 0s are met in column B, with condition that
the
subsequent row (row 4 for above) if cell A4 is a positive digit
(+0.5
in
this
case). If YES, count the sequence everytime it happens within the
column
(if
NO, don't count).
=FORMULA?

b) While (a) just count the number of times it occurs, another
formula
is
required to count if condition (a) is met, AND (B4-C4)+A4=+ve number
then
count how many times such an event happens within the column.


For both the above, it must be noted that in the event another 0
appears
after the two (2) consecutive 0s (see B7:B9), the formula will still
remain
and count the subsequent row based on the conditions provided in (a)
and
(b).
Is this possible?

HELP! Thanks!

 

[andrew]

Hi Biff, file has been sent.

i don't see any e-mail addy below

No, it's there. It's just disguised to keep the spam trawlers from
recognizing it.

Here it is again:

xl can help at comcast period net

Rmove the word "can". Replace "at" and "period" with their respective
symbols and remove all the spaces.

--
Biff
Microsoft Excel MVP

Err, i don't see any e-mail addy below except in your profile (which i
tried
to send but mail bounced..). Can you send a test e-mail to me so that i
can
reply with the data for your review? Thanks!

i can e-mail you a sample sheet with the actual data and formulas

OK, let's see what we're dealing with! I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


Hi Biff, firstly thanks for being patient and helping me out with your
various formulas. You've been great! Lori too, and she was close the
last
time...

Anyway, let me see if i can explain clearer. What i intend to do from
the
table is to analyse for sequences of zeros. What the table shows are
the
results of matches played by a team. The +0.5 or -0.5 are just
handicaps
that
the team provides to its opponent. The results are updated weekly in a
column
from the fixtures played over the year. Now, here's the actual part of
analysis which i'm trying to derive with formulas:

1) To check for sequences of two (2) zeroes on column B (score of home
team)
with the conditions provided in (a) per original post. As for (b), it
is
just
a total count of the occurence regardless if the result [(column B
minus
column C) + column A] is a positive value or not.

So, as an example, if i were to refer to the original table posted, B2
and
B3 had 0s (i.e. the team did not score consecutively in 2 matches). On
B4
(subsequent row), the team played to a 1-1 draw, with a handicap of
+0.5.
From this example, the formula will count this as 1 instance as it
returned a
positive value ,i.e.
(1-1)+0.5 = +0.5
IF the result had been 1-2 for row B4 and C4, then it return a negative
value, i.e.
(1-2)+0.5 = -0.5 (which the formula will not count as 1 instance)


If the above is possible, then i'm actually looking at a table that
stores
the following information:
i) sequence of two 0s, with +0.5 handicap (another column will store
the
total count of such occurence regardless if the subsequent row returned
a
positive or negative value for row (B-C)+A)
ii) sequence of two 0s, giving -0.5 handicap

Does the above make sense? If not, i can e-mail you a sample sheet with
the
actual data and formulas....Thanks in advance!


:

its for a football statistics

Maybe if you explained what this data actually means we might be able
to
come up with something that's workable.

If you recall, I replied to a couple of your previous posts and I had
my
doubts if what you're asking for is even possible. It looks like my
doubts
have proven to be accurate! <g> Lori also replied and between us we
couldn't
come up with anything that worked. Lori is a top-notch formula person!
I'm
not a slouch, either!


--
Biff
Microsoft Excel MVP


HELP, i'm going bonkers with zeroes. I've actually posted this quite
a
while
back, and worked on various suggested formulas but to no avail. I
hope
you
can be patient as i will have to explain quite elaborately due to
the
nature
of the required results. Here's what been bugging me (its for a
football
statistics):

A B C
-0.5 1 0
+0.5 0 0
+0.5 0 1
+0.5 1 1
-0.5 0 0
-0.5 1 0
+0.5 0 2
-0.5 0 1
-0.5 0 3
+0.5 1 1

I would like to count the following:
a) two (2) consecutive 0s are met in column B, with condition that
the
subsequent row (row 4 for above) if cell A4 is a positive digit
(+0.5
in
this
case). If YES, count the sequence everytime it happens within the
column
(if
NO, don't count).
=FORMULA?

b) While (a) just count the number of times it occurs, another
formula
is
required to count if condition (a) is met, AND (B4-C4)+A4=+ve number
then
count how many times such an event happens within the column.


For both the above, it must be noted that in the event another 0
appears
after the two (2) consecutive 0s (see B7:B9), the formula will still
remain
and count the subsequent row based on the conditions provided in (a)
and
(b).
Is this possible?

HELP! Thanks!