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:

[TABLE="width: 177"]
<tbody>[TR]
[TD]23/02/2013[/TD]
[TD]$53.88[/TD]
[/TR]
[TR]
[TD]3/03/2013[/TD]
[TD]$47.80[/TD]
[/TR]
[TR]
[TD]16/03/2013[/TD]
[TD]$53.16[/TD]
[/TR]
[TR]
[TD]20/03/2013[/TD]
[TD]$87.81[/TD]
[/TR]
[TR]
[TD]3/04/2013[/TD]
[TD]$31.88[/TD]
[/TR]
[TR]
[TD]4/04/2013[/TD]
[TD]$79.28[/TD]
[/TR]
[TR]
[TD]17/04/2013[/TD]
[TD]$43.41[/TD]
[/TR]
[TR]
[TD]23/04/2013[/TD]
[TD]$70.37[/TD]
[/TR]
[TR]
[TD]2/05/2013[/TD]
[TD]$68.39[/TD]
[/TR]
[TR]
[TD]23/05/2013[/TD]
[TD]$84.36[/TD]
[/TR]
[TR]
[TD]26/05/2013[/TD]
[TD]$48.16[/TD]
[/TR]
[TR]
[TD]20/06/2013[/TD]
[TD]$75.87[/TD]
[/TR]
[TR]
[TD]22/06/2013[/TD]
[TD]$45.74[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 177"]
<tbody>[TR]
[TD="align: right"]23/02/2013
[/TD]
[TD="align: right"]$53.88[/TD]
[/TR]
[TR]
[TD="align: right"]3/03/2013[/TD]
[TD="align: right"]$47.80[/TD]
[/TR]
[TR]
[TD="align: right"]16/03/2013[/TD]
[TD="align: right"]$53.16[/TD]
[/TR]
[TR]
[TD="align: right"]....etc....[/TD]
[TD="align: right"]...etc...[/TD]
[/TR]
</tbody>[/TABLE]
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.