Days Moving Average with Non-Consecutive Days Data

ridgeroad1

New Member
Joined
Mar 22, 2014
Messages
1
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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
    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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,079
Messages
5,466,492
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top