I think the manual formulas are not correct.
For example, let's say StartDate = 1 Jan, EndDate = 2 Jan, Cost = 200
The average cost should be : 200 / (EndDate - StartDate + 1) = 100 --> (1 Jan) 100 + (2 Jan) 100 = 200 --> correct
If you use : 200 / (EndDate - StartDate) = 200 --> (1 Jan) 200 + (2 Jan) 200 = 400 --> not correct
So formula on F9 should be :
Formula:
=(C3/(B3-A3+1))*(EOMONTH(A3,0)-A3+1)
and formula on J9 should be :
Formula:
=(C3/(B3-A3+1))*DAY(B3)
The UDF below is using (EndDate - StartDate + 1) syntax, but if you still want the original formula, then uncomment the three lines (and remove 3 lines above them).
Function Test(startDate As Date, endDate As Date, amount As Double, iMonth As Long)
Dim EO As Date, dd As Long, v1 As Double, v2 As Double, v3 As Double
EO = DateSerial(Year(startDate), Month(startDate) + 1, 1) - 1
dd = Month(endDate) - Month(startDate)
If dd = 0 Then v1 = amount Else v1 = (EO - startDate + 1) / (endDate - startDate + 1) * amount
If dd > 0 Then v3 = Day(endDate) / (endDate - startDate + 1) * amount
If dd > 1 Then v2 = (amount - (v1 + v3)) / (dd - 1)
'If dd = 0 Then v1 = amount Else v1 = (EO - startDate + 1) / (endDate - startDate) * amount
'If dd > 0 Then v3 = Day(endDate) / (endDate - startDate) * amount
'If dd > 1 Then v2 = (amount - (v1 + v3)) / (dd - 1)
If iMonth = Month(startDate) Then
Test = v1
ElseIf iMonth = Month(endDate) Then
Test = v3
ElseIf iMonth > Month(startDate) And iMonth < Month(endDate) Then
Test = v2
Else
Test = ""
End If
End Function
Usage :
Formula on D3 :
Formula:
=test($A$3,$B$3,$C$3,COLUMN()-3)
and copy to right
Bookmarks