Days Moving Average with Non-Consecutive Days Data

[ridgeroad1]

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

 

[joeu2004]

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:

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
    Sheets.Add before:=Sheets(1)
    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.