Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the amount of
gas (MCF) that flowed from a gaswell for that day. I want to count the number
of current days the gaswell has NOT been producing or is at (0). In this
example the formula would begin counting - 4 days at zero, then the well
produced 5 MCF so the formula goes back to 0. It starts again on day six
counting 3 days of zero then sees there is 2 MCF so it goes back to zero and
starts over again. It sees on day 9 there is 2 MCF and goes back to zero
again and starts over counting. The end result for this example is the well
currently has not produced for 5 days. If anyone can help with this formula
I'd sure appreciate it! THANKS!!!!!

For lack of anything more elegant, this will take care of a week........

=IF(N10,"",IF(M10,1,IF(L10,2,IF(K10,3,IF(J10, 4,IF(I10,5,IF(H10,6,IF(G10,7,"more than 7 days"))))))))

Vaya con Dios,
Chuck, CABGx3




"Jannie" wrote:

Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the amount of
gas (MCF) that flowed from a gaswell for that day. I want to count the number
of current days the gaswell has NOT been producing or is at (0). In this
example the formula would begin counting - 4 days at zero, then the well
produced 5 MCF so the formula goes back to 0. It starts again on day six
counting 3 days of zero then sees there is 2 MCF so it goes back to zero and
starts over again. It sees on day 9 there is 2 MCF and goes back to zero
again and starts over counting. The end result for this example is the well
currently has not produced for 5 days. If anyone can help with this formula
I'd sure appreciate it! THANKS!!!!!

Jannie wrote:
Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the amount of
gas (MCF) that flowed from a gaswell for that day. I want to count the number
of current days the gaswell has NOT been producing or is at (0).

I think you want to count the number __consecutive__ days that the
gaswell is not producing (i.e. is zero). Right?

In this
example the formula would begin counting - 4 days at zero, then the well
produced 5 MCF so the formula goes back to 0. It starts again on day six
counting 3 days of zero then sees there is 2 MCF so it goes back to zero and
starts over again. It sees on day 9 there is 2 MCF and goes back to zero
again and starts over counting. The end result for this example is the well
currently has not produced for 5 days.

Suppose those numbers are in B1:O1. In row 2 (which you can hide), put
the following formulas into B2 and B3, then copying right:

B2: =if(B1<0, 0, A2+1)
B3: =if(C1<0, B2, "")

Row 2 accumulates the number of consecutive zeros. Row 3 shows the
number of consecutive zeros under the last zero of a sequence.

There are two special cases to consider: the first column of data and
the last column of data. As I noted, if you start your data in column
B and keep A2 blank, there is no special case to worry about. And if
you terminate your data with a non-zero value (e.g. -1, which can be
hidden), there is no special case to worry about.

If you do not want to handle the special cases in that way and if you
do not know how to test for the first and last columns of data, post
back here, and I or someone can help you further.

Here's a maybe solution. Assuming the data you've show us starts in A1 and
continues over to N1, put this cell in B2 and make sure you get the $ symbols
in it where they are shown:
=IF(B10,COUNTIF($A$1:B1,0)-SUM($A$2:A2),"")
Now just extend that formula over as far as your data goes (to column N for
now).

The catch on this one is that a number of non-producing days is not shown
until a producing day shows up in the data. So you'll notice that no value
shows up in columns J thru N in your example. But even if there were many
days of non-production, you could get a count by temporarily entering a
non-zero number into row 1 (and extending the formula).



"Jannie" wrote:

Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the amount of
gas (MCF) that flowed from a gaswell for that day. I want to count the number
of current days the gaswell has NOT been producing or is at (0). In this
example the formula would begin counting - 4 days at zero, then the well
produced 5 MCF so the formula goes back to 0. It starts again on day six
counting 3 days of zero then sees there is 2 MCF so it goes back to zero and
starts over again. It sees on day 9 there is 2 MCF and goes back to zero
again and starts over counting. The end result for this example is the well
currently has not produced for 5 days. If anyone can help with this formula
I'd sure appreciate it! THANKS!!!!!

With a minor modification, the total number of consecutive 0 days can be
shown below the last 0 day in each series. Put this in B2 instead of the
other:
=IF(C10,COUNTIF($A$1:B1,0)-SUM($A$2:A2),"")

"JLatham" wrote:

Here's a maybe solution. Assuming the data you've show us starts in A1 and
continues over to N1, put this cell in B2 and make sure you get the $ symbols
in it where they are shown:
=IF(B10,COUNTIF($A$1:B1,0)-SUM($A$2:A2),"")
Now just extend that formula over as far as your data goes (to column N for
now).

The catch on this one is that a number of non-producing days is not shown
until a producing day shows up in the data. So you'll notice that no value
shows up in columns J thru N in your example. But even if there were many
days of non-production, you could get a count by temporarily entering a
non-zero number into row 1 (and extending the formula).



"Jannie" wrote:

Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the amount of
gas (MCF) that flowed from a gaswell for that day. I want to count the number
of current days the gaswell has NOT been producing or is at (0). In this
example the formula would begin counting - 4 days at zero, then the well
produced 5 MCF so the formula goes back to 0. It starts again on day six
counting 3 days of zero then sees there is 2 MCF so it goes back to zero and
starts over again. It sees on day 9 there is 2 MCF and goes back to zero
again and starts over counting. The end result for this example is the well
currently has not produced for 5 days. If anyone can help with this formula
I'd sure appreciate it! THANKS!!!!!

If I understand what you're asking for,
A *single* formula to return the number of the *latest*, consecutive,
non-productive days ( 0's ).

With your data in say Row1, from say A to AA,
Try this *array* formula:

=COUNT(A1:AA1)-MAX(IF(A1:AA1<0,COLUMN(A1:AA1)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jannie" wrote in message
...
Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the amount
of
gas (MCF) that flowed from a gaswell for that day. I want to count the
number
of current days the gaswell has NOT been producing or is at (0). In this
example the formula would begin counting - 4 days at zero, then the well
produced 5 MCF so the formula goes back to 0. It starts again on day six
counting 3 days of zero then sees there is 2 MCF so it goes back to zero
and
starts over again. It sees on day 9 there is 2 MCF and goes back to zero
again and starts over counting. The end result for this example is the
well
currently has not produced for 5 days. If anyone can help with this
formula
I'd sure appreciate it! THANKS!!!!!

JLatham wrote:
=IF(B10,COUNTIF($A$1:B1,0)-SUM($A$2:A2),"")

I like it. Concise; and it does not require any helper cells.

Thanks! I'm going to try this and see how it works. I appreciate everyone's
help with this. This report is very important to us, so I hope this works,
if not, I'll be back! :) Thanks everyone. - Jannie

"Ragdyer" wrote:

If I understand what you're asking for,
A *single* formula to return the number of the *latest*, consecutive,
non-productive days ( 0's ).

With your data in say Row1, from say A to AA,
Try this *array* formula:

=COUNT(A1:AA1)-MAX(IF(A1:AA1<0,COLUMN(A1:AA1)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jannie" wrote in message
...
Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the amount
of
gas (MCF) that flowed from a gaswell for that day. I want to count the
number
of current days the gaswell has NOT been producing or is at (0). In this
example the formula would begin counting - 4 days at zero, then the well
produced 5 MCF so the formula goes back to 0. It starts again on day six
counting 3 days of zero then sees there is 2 MCF so it goes back to zero
and
starts over again. It sees on day 9 there is 2 MCF and goes back to zero
again and starts over counting. The end result for this example is the
well
currently has not produced for 5 days. If anyone can help with this
formula
I'd sure appreciate it! THANKS!!!!!

All of us here really appreciate it when you "get back" to us when it *does*
work, as well as when it doesn't.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jannie" wrote in message
...
Thanks! I'm going to try this and see how it works. I appreciate
everyone's
help with this. This report is very important to us, so I hope this
works,
if not, I'll be back! :) Thanks everyone. - Jannie

"Ragdyer" wrote:

If I understand what you're asking for,
A *single* formula to return the number of the *latest*, consecutive,
non-productive days ( 0's ).

With your data in say Row1, from say A to AA,
Try this *array* formula:

=COUNT(A1:AA1)-MAX(IF(A1:AA1<0,COLUMN(A1:AA1)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jannie" wrote in message
...
Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the
amount
of
gas (MCF) that flowed from a gaswell for that day. I want to count the
number
of current days the gaswell has NOT been producing or is at (0). In
this
example the formula would begin counting - 4 days at zero, then the
well
produced 5 MCF so the formula goes back to 0. It starts again on day
six
counting 3 days of zero then sees there is 2 MCF so it goes back to
zero
and
starts over again. It sees on day 9 there is 2 MCF and goes back to
zero
again and starts over counting. The end result for this example is the
well
currently has not produced for 5 days. If anyone can help with this
formula
I'd sure appreciate it! THANKS!!!!!

Good point RD.........many times we post a response to an OP's question and
never hear from them again. The problem here in many cases is not to be
able to offer a correct Excel solution, but mostly to be able to figure out
from a cryptic query as to exactly what the OP really wants to know. When
we don't hear back, we don't know if the OP was happy with our offering, or
wasn't able to incorporate/understand it, or worse yet, we may have
misunderstood their problem and offered the wrong solution.....so they just
go away thinking they won't be able to do what they want to......bummer
there, for we're here to help. It's a good thing when the OP's heed your
suggestion and give closure to their original post.

Thanks for making the point.

Vaya con Dios,
Chuck, CABGx3




"Ragdyer" wrote in message
...
All of us here really appreciate it when you "get back" to us when it
*does*
work, as well as when it doesn't.
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Jannie" wrote in message
...
Thanks! I'm going to try this and see how it works. I appreciate
everyone's
help with this. This report is very important to us, so I hope this
works,
if not, I'll be back! :) Thanks everyone. - Jannie

"Ragdyer" wrote:

If I understand what you're asking for,
A *single* formula to return the number of the *latest*, consecutive,
non-productive days ( 0's ).

With your data in say Row1, from say A to AA,
Try this *array* formula:

=COUNT(A1:AA1)-MAX(IF(A1:AA1<0,COLUMN(A1:AA1)))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD


-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so all may benefit
!

-------------------------------------------------------------------------
--
"Jannie" wrote in message
...
Example: I have a row of numbers - 0 0 0 0 5 0 0 0 2 0 0 0 0 0.
Each number is in a different column. Each number represents the
amount
of
gas (MCF) that flowed from a gaswell for that day. I want to count
the
number
of current days the gaswell has NOT been producing or is at (0). In
this
example the formula would begin counting - 4 days at zero, then the
well
produced 5 MCF so the formula goes back to 0. It starts again on day
six
counting 3 days of zero then sees there is 2 MCF so it goes back to
zero
and
starts over again. It sees on day 9 there is 2 MCF and goes back to
zero
again and starts over counting. The end result for this example is
the
well
currently has not produced for 5 days. If anyone can help with this
formula
I'd sure appreciate it! THANKS!!!!!