# Days Moving Average with Non-Consecutive Days Data

##### New Member
Hello,

This is part of my data set on my fuel spending which I am trying to analyse:

 23/02/2013 \$53.88 3/03/2013 \$47.80 16/03/2013 \$53.16 20/03/2013 \$87.81 3/04/2013 \$31.88 4/04/2013 \$79.28 17/04/2013 \$43.41 23/04/2013 \$70.37 2/05/2013 \$68.39 23/05/2013 \$84.36 26/05/2013 \$48.16 20/06/2013 \$75.87 22/06/2013 \$45.74

<tbody>
</tbody>

To make sense of this data it would seem that a 14 or 28 Day moving average would be useful. I have tried charting this in Excel but it seems that Excel wants to give me the period moving average which means that if I spend 3 x \$50 on fuel in march and 1 x \$50 of fuel in February that the average stays the same when I have in fact spent far more in March.

Any help would be greatly appreciated.

Thanks,

Daniel

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### joeu2004

##### Well-known Member
This is part of my data set on my fuel spending which I am trying to analyse:
 23/02/2013 \$53.88 3/03/2013 \$47.80 16/03/2013 \$53.16 ....etc.... ...etc...

<tbody>
</tbody>
To make sense of this data it would seem that a 14 or 28 Day moving average would be useful. I have tried charting this in Excel but it seems that Excel wants to give me the period moving average which means that if I spend 3 x \$50 on fuel in march and 1 x \$50 of fuel in February that the average stays the same when I have in fact spent far more in March.
I would convert the data to a daily average. For example, \$53.88 spent on 23/02/2013 translates into a daily average of \$6.735 for 23/02/2013 through 2/03/2013. Select the data above and use the following macro to convert it:
Code:
``````Sub doit()
Dim v As Variant
Dim n As Long, i As Long, j As Long, k As Long
Dim d As Double, a As Double
v = Selection.Value2
n = UBound(v)
ReDim res(1 To v(n, 1) - v(1, 1), 1 To 2) As Variant
ActiveSheet.Name = "daily"
k = 0
For i = 1 To n - 1
d = v(i + 1, 1) - v(i, 1)    ' days
a = v(i, 2) / d              ' daily average
For j = 0 To d - 1
k = k + 1
res(k, 1) = CDate(v(i, 1) + j)
res(k, 2) = a
Next
Next
Range("a1:b" & k) = res
End Sub``````
There is still an issue with using Data Analysis or chart trendline to create the moving average. I think a 28-day leading moving average is useful. But the chart trendline is limited to a 12-point moving average. And both tools create a trailing moving average.

So I would create the moving average myself. If the daily average data is in B1:B119 (with dates in A1:A119), insert a column before column B (so the original data is now in C1:C119), enter =AVERAGE(C1:C28) into B1, and copy down through B92. To chart, select A1:B92, then insert a Scatter chart with Smooth Lines, for example.