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

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

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,744
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,102,360
Messages
5,486,387
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top