Calculate rolling average using last 30 values greater than 0.

[Duke Joel]

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!

 

[Mike H]

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]

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

 

[Duke Joel]

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