CODEWITHSUNDEEP
excel
Brian
Brian

Reputation: 117

Calculating weighted monthly average from daily values, including missing data

I have a list of price changes for a product over many years, with associated dates of changes in the price down to the day. However, these prices change infrequently, meaning I may have only 4 data points in one year. What is most important is that in between these price change points, prices remain constant from the last reported point, even though they are not listed in the data.

How do I find the weighted monthly average, accounting for the days in which data is not shown?

Excel data

The average for Item3 in January 2015, for example, should end up a weighted average of 21 days of prices at 110, 7 days of prices at 94 and 3 days of prices at 88, averaged over the 31 days of January. This is therefore 104.26

I've tried using the suggested formula in a previously asked question, but what that does is simply take the average of values listed in January 2015, for example. That gives the answer of 97.33 instead of what should be 104.26

The final output table should be a time series table, starting with January to December for every year. For example:

Ideal output

Upvotes: 3

Views: 631

Answers (2)

Skin
Skin

Reputation: 11197

See if this works for you. Paste the below code into a new module in VBA ...

Public Function CalculateAveragePrice(ByVal strItemNo As String, ByVal dtMonth As Date, ByVal rngData As Range) As Variant
    Dim lngRow As Long, lngCol As Long, lngItemCol As Long, i As Long, bFoundTop As Boolean, lngYear As Long
    Dim dtThisDate As Date, dtComparisonDate As Date, arrDays() As Double, dtNextDate As Date, lngMonth As Long
    Dim lngBottomRow As Long, lngTopRow As Long, lngDaysInMonth As Long, dblCurrentPrice As Double

    ' Initialise the array with something.
    ReDim arrDays(0)

    lngMonth = Month(dtMonth)
    lngYear = Year(dtMonth)

    With rngData
        ' Find the header column for the item
        For lngItemCol = 1 To .Columns.Count
            If .Cells(1, lngItemCol) = strItemNo Then Exit For
        Next

        For i = 1 To 2
            If i = 1 Then
                dtComparisonDate = DateSerial(Year(dtMonth), Month(dtMonth), 1)
            Else
                dtComparisonDate = DateAdd("d", -1, DateAdd("m", 1, dtMonth))
                lngDaysInMonth = Day(dtComparisonDate)
                ReDim Preserve arrDays(lngDaysInMonth)
            End If

            For lngRow = 2 To .Rows.Count
                dtThisDate = .Cells(lngRow, 1)

                If i = 1 Then
                    If dtThisDate < dtComparisonDate Then lngBottomRow = lngRow
                Else
                    If dtThisDate > dtComparisonDate And Not bFoundTop Then
                        lngTopRow = lngRow
                        bFoundTop = True
                    End If
                End If
            Next
        Next

        If lngTopRow = 0 Then lngTopRow = .Rows.Count
        If lngBottomRow = 0 Then lngBottomRow = 2

        For i = 1 To UBound(arrDays)
            For lngRow = lngTopRow To lngBottomRow Step -1
                dtThisDate = .Cells(lngRow, 1)
                dblCurrentPrice = .Cells(lngRow, lngItemCol)

                If dtThisDate <= DateSerial(lngYear, lngMonth, i) Then
                    arrDays(i) = dblCurrentPrice + arrDays(i - 1)
                    Exit For
                End If
            Next
        Next

        If UBound(arrDays) > 0 Then CalculateAveragePrice = arrDays(UBound(arrDays)) / UBound(arrDays)
    End With
End Function

... and then setup your formula to look like the below ...

Formula Setup

The code will also work for dates well outside the range of given prices. That may not be important for you but just something to note.

There's probably a more elegant way but it works for me and I trust it will work for you too.

See how it goes.

Upvotes: 3

Tom Sharpe
Tom Sharpe

Reputation: 34210

An array formula solution would look like this:

=AVERAGE(
      IF((ROW(INDEX($A:$A,MIN($B$3:$B$20)):INDEX($A:$A,DATE(MAX(YEAR($B$3:$B$20)),12,31)))>=$G3)
     *(ROW(INDEX($A:$A,MIN($B$3:$B$20)):INDEX($A:$A,DATE(MAX(YEAR($B$3:$B$20)),12,31)))<=EOMONTH($G3,0)),
      INDEX(C$3:C$20,N(IF({1},MATCH(ROW(INDEX($A:$A,MIN($B$3:$B$20)):INDEX($A:$A,DATE(MAX(YEAR($B$3:$B$20)),12,31))),$B$3:$B$20))))
     )
)

It's basically the same as using a helper column to generate a date for each day across the whole range, except that it's generated inside the formula by the ROW(INDEX...)) statements.

Must be entered using CtrlShiftEnter

enter image description here

Upvotes: 2

Related Questions