Calculate rolling average using last 30 values greater than 0.
 

I have two columns of data, production in tons and emissions in lbs for each
calendar day, no missing data, but can have values=0 (no production).
What I what to do is calculate a 30-day rolling average of emission
lbs/production tons using the last 30 production values that are greater than
zero.
For simplicity, in example below, assume I am looking for a 5-day rolling
average
A B C D
Date Production Emissions 5-day average
(tons) (lbs) (lb/ton)
1/1/09 10,000 2,000 n/a
1/2/09 5,000 1,000 n/a
1/3/09 5,000 1,000 n/a
1/4/09 5,000 1,000 n/a
1/5/09 5,000 1,000 0.200 (=6,000 lbs/30,000 tons)
1/6/09 8,000 1,000 0.179 (=5,000 lbs/28,000 tons)
1/7/09 0 0 0.179 (=5,000
lbs/28,000 tons)
1/8/09 0 0 0.179 (=5,000
lbs/28,000 tons)
1/8/09 9,000 3,000 0.219 (=7,000 lbs/32,000 tons)

Thanks in advance!

Calculate rolling average using last 30 values greater than 0.
 

Hi,

I'm assuming your data start in row 2, Put this in D2 and array enter (See
below) drag down and it will produce a #NUM! error until you get to D6, from
then on it will calculate the last 5 rows in col c/last 5 in col B

=(SUM(INDEX($C$2:C2,LARGE(ROW($C$2:C2)*($C$2:C2<0 ),5)):C2))/SUM(INDEX($B$2:B2,LARGE(ROW($B$2:B2)*($B$2:B2<0), 5)):B2)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Duke Joel" wrote:

I have two columns of data, production in tons and emissions in lbs for each
calendar day, no missing data, but can have values=0 (no production).
What I what to do is calculate a 30-day rolling average of emission
lbs/production tons using the last 30 production values that are greater than
zero.
For simplicity, in example below, assume I am looking for a 5-day rolling
average
A B C D
Date Production Emissions 5-day average
(tons) (lbs) (lb/ton)
1/1/09 10,000 2,000 n/a
1/2/09 5,000 1,000 n/a
1/3/09 5,000 1,000 n/a
1/4/09 5,000 1,000 n/a
1/5/09 5,000 1,000 0.200 (=6,000 lbs/30,000 tons)
1/6/09 8,000 1,000 0.179 (=5,000 lbs/28,000 tons)
1/7/09 0 0 0.179 (=5,000
lbs/28,000 tons)
1/8/09 0 0 0.179 (=5,000
lbs/28,000 tons)
1/8/09 9,000 3,000 0.219 (=7,000 lbs/32,000 tons)

Thanks in advance!

Calculate rolling average using last 30 values greater than 0.
 

Your post fixed the problem of properly handling zeros, however one quirk
came up. The calculation only got the rolling average of the last 4 days for
some reason. I changed the two 5's in your formula to 6's and now it is
properly calculating a 5-day average.

Sample data using 5's:
Date Production Emissions Emis Rate 5-day avg
1/1/2009 5,500 2,200 #NUM!
1/2/2009 5,900 2,500 #NUM!
1/3/2009 5,800 2,300 #NUM!
1/4/2009 6,100 1,600 #NUM! should be
1/5/2009 5,100 1,900 0.362 0.370
1/6/2009 6,200 1,800 0.328 0.347
1/7/2009 5,900 1,700 0.300 0.320

Sample data using 6's:
Date Production Emissions Emis Rate 5-day avg
1/1/2009 5,500 2,200 #NUM!
1/2/2009 5,900 2,500 #NUM!
1/3/2009 5,800 2,300 #NUM!
1/4/2009 6,100 1,600 #NUM!
1/5/2009 5,100 1,900 #NUM!
1/6/2009 6,200 1,800 0.347
1/7/2009 5,900 1,700 0.320


"Mike H" wrote:

Hi,

I'm assuming your data start in row 2, Put this in D2 and array enter (See
below) drag down and it will produce a #NUM! error until you get to D6, from
then on it will calculate the last 5 rows in col c/last 5 in col B

=(SUM(INDEX($C$2:C2,LARGE(ROW($C$2:C2)*($C$2:C2<0 ),5)):C2))/SUM(INDEX($B$2:B2,LARGE(ROW($B$2:B2)*($B$2:B2<0), 5)):B2)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Duke Joel" wrote:

I have two columns of data, production in tons and emissions in lbs for each
calendar day, no missing data, but can have values=0 (no production).
What I what to do is calculate a 30-day rolling average of emission
lbs/production tons using the last 30 production values that are greater than
zero.
For simplicity, in example below, assume I am looking for a 5-day rolling
average
A B C D
Date Production Emissions 5-day average
(tons) (lbs) (lb/ton)
1/1/09 10,000 2,000 n/a
1/2/09 5,000 1,000 n/a
1/3/09 5,000 1,000 n/a
1/4/09 5,000 1,000 n/a
1/5/09 5,000 1,000 0.200 (=6,000 lbs/30,000 tons)
1/6/09 8,000 1,000 0.179 (=5,000 lbs/28,000 tons)
1/7/09 0 0 0.179 (=5,000
lbs/28,000 tons)
1/8/09 0 0 0.179 (=5,000
lbs/28,000 tons)
1/8/09 9,000 3,000 0.219 (=7,000 lbs/32,000 tons)

Thanks in advance!

Calculate rolling average using last 30 values greater than 0.
 

Mike,

One revision to my original question if I may. If there are emissions, I
want to include that day's emissions and production in the calculation even
if the production is 0. In the example below, the current formula incorrectly
divides 1/3-1/7 emissions by 1/2-1/6 production. This may make this quite
complicated, if so I can manage by entering a value of "1" on days there are
emissions but no production.

Date Production Emissions 5-day avg rate
1/1/2009 5,500 2,200 #NUM!
1/2/2009 5,900 2,500 #NUM!
1/3/2009 5,800 2,300 #NUM!
1/4/2009 6,100 1,600 #NUM!
1/5/2009 5,100 1,900 #NUM! should be
1/6/2009 6,200 1,800 0.347 0.347
1/7/2009 0 1,000 0.296 0.371


"Mike H" wrote:

Hi,

I'm assuming your data start in row 2, Put this in D2 and array enter (See
below) drag down and it will produce a #NUM! error until you get to D6, from
then on it will calculate the last 5 rows in col c/last 5 in col B

=(SUM(INDEX($C$2:C2,LARGE(ROW($C$2:C2)*($C$2:C2<0 ),5)):C2))/SUM(INDEX($B$2:B2,LARGE(ROW($B$2:B2)*($B$2:B2<0), 5)):B2)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Duke Joel" wrote:

I have two columns of data, production in tons and emissions in lbs for each
calendar day, no missing data, but can have values=0 (no production).
What I what to do is calculate a 30-day rolling average of emission
lbs/production tons using the last 30 production values that are greater than
zero.
For simplicity, in example below, assume I am looking for a 5-day rolling
average
A B C D
Date Production Emissions 5-day average
(tons) (lbs) (lb/ton)
1/1/09 10,000 2,000 n/a
1/2/09 5,000 1,000 n/a
1/3/09 5,000 1,000 n/a
1/4/09 5,000 1,000 n/a
1/5/09 5,000 1,000 0.200 (=6,000 lbs/30,000 tons)
1/6/09 8,000 1,000 0.179 (=5,000 lbs/28,000 tons)
1/7/09 0 0 0.179 (=5,000
lbs/28,000 tons)
1/8/09 0 0 0.179 (=5,000
lbs/28,000 tons)
1/8/09 9,000 3,000 0.219 (=7,000 lbs/32,000 tons)

Thanks in advance!