Calculating averages over irregular time periods?

Question

I'm just a beginner in VBA, so it would be really nice if somebody could me help out a bit here:

I have a table with yearly sales data like:

Year 2007: 15
Year 2008: 20
Year 2009: 25
Year 2010: 30

However, it could be that an item only started selling in 2007 on September 1 (09/01/2007) and ended selling in 2010 on April 1/2010 (04/01/2010).

Now I want to calculate one overall yearly average. The way I do it is to it all by hand:

Calculate the days the product sold in 2007 (09/01/2007 - 01/01/2007 = 243 days)
Take the ratio (243/365 = 0,665753) and multiply that with the sales of 2007. 2008 and 2009 are full years, so both * 1. For 2010, I again calculate the ratio 0,246576 [90/365 days].
Then I sum all these values and divide them by 2,912329 (0,665753 + 0,246575 + 1 + 1, because 2008/2009 are both full years).

It would make my life so much easier if I could give excel a date range (09/01/2007 to 04/01/2010) and it would give me the output:

0,665753 (243/365 for 2007)
1 (for full year 2008)
1 (for full year 2009)
0,246575 (ratio 90/365 for 2010)

Please see image below to make it a bit easier to understand:

You didn't show us your data layout, where do you have your start selling data and end selling date on your spreadsheet? It would be a lot easier to formulate an answer if you could show us an image or something, also if you have tried any code at all you should show this too... — Xabier
Thanks! That's a good point, I uploaded an image here (imgur.com/a/w7lWq) to make it a bit easier to understand. So far, I have not used code, mostly doing it by hand. — Julia T.

Xabier Xabier · Accepted Answer · 2018-01-25T11:53:48

All you need for this to work is a beginning date and end date on cells G1 and G2, and the sales per year on Columns A & B, then the code will populate your Factor and Multiplied on columns C & D, and give you a result on cell A10. So you wouldn't need anything below row 13 in your example.

Sub foo()

Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set your worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
'get the last row with data on Column A

BeginningDate = Format(ws.Range("G1"), "dd/mm/yyyy") 'get the beginning date from cell G1
EndDate = Format(ws.Range("G2"), "dd/mm/yyyy") 'get the end date from cell G2

Years = DateDiff("yyyy", BeginningDate, EndDate) + 1 'get the number of years to run a loop

CurrentDate = BeginningDate

For i = 1 To Years 'loop as many years between dates
    LastDayOfYear = "31/12/" & Year(CurrentDate) 'get the last day of the given year
    DaysDiff = DateDiff("d", CurrentDate, EndDate) 'calculate the days between Current and EndDate
    If DaysDiff > 365 Then 'if more than a year between
        CurrentDays = DateDiff("d", CurrentDate, LastDayOfYear)
        If CurrentDays = 364 Then CurrentDays = 365 'uncomment this line if you want your calculations to assume every year has 365 days
        Factor = CurrentDays / 365
        ws.Cells(i + 1, 3) = Factor
        ws.Cells(i + 1, 4) = Factor * ws.Cells(i + 1, 2)
        CurrentDate = DateAdd("d", 1, LastDayOfYear)
    Else
        Factor = DaysDiff / 365
        ws.Cells(i + 1, 3) = Factor
        ws.Cells(i + 1, 4) = Factor * ws.Cells(i + 1, 2)
        CurrentDate = DateAdd("d", 1, LastDayOfYear)
    End If
Next i
ws.Range("A10").Value = WorksheetFunction.Sum(ws.Range("D2:D" & Years + 1)) / WorksheetFunction.Sum(ws.Range("C2:C" & Years + 1))
End Sub

Calculating averages over irregular time periods?

1 Answers