mduff
04-28-2009, 07:31 AM
Hi I found these 3 UDF functions to calculate forecast accuracy on the internet but what I need to do is have them calculate on criteria.
Really what I need is these functions to work like a SUMIF So I have a data sheet that has dates in and data for Forecast and Actual so what I would need is the UDFs to only calculate for the dates selected
Example
01/01/2009 MAPE only for data that has dates of 01/01/2009
01/02/2009 MAPE only for data that has dates of 01/02/2009
So at least in my mind it would be like a MAPEIF formula to Only calculate the MAPE (ABS(Actual-Forecast)/Actual) a date is in another row same for the MAD and MSE
Not even sure if this is possible but hoping someone can help with it
Thanks in advance
Function MAPE(Actual As Range, Forecast As Range)
Dim i As Integer
Dim total1 As Single
total1 = 0
For i = 1 To Actual.Rows.Count
total1 = total1 + Abs(Actual.Cells(i, 1) - Forecast.Cells(i, 1)) / Actual.Cells(i, 1)
Next
MAPE = total1 * 100 / Actual.Rows.Count
End Function
Function MAD(Actual As Range, Forecast As Range)
Dim i As Integer
Dim total1 As Single
total1 = 0
For i = 1 To Actual.Rows.Count
total1 = total1 + Abs(Actual.Cells(i, 1) - Forecast.Cells(i, 1))
Next
MAD = total1 / Actual.Rows.Count
End Function
Function MSE(Actual As Range, Forecast As Range)
Dim i As Integer
Dim total1 As Single
total1 = 0
For i = 1 To Actual.Rows.Count
total1 = total1 + Abs(Actual.Cells(i, 1) - Forecast.Cells(i, 1)) ^ 2
Next
MSE = total1 / Actual.Rows.Count
End Function
Really what I need is these functions to work like a SUMIF So I have a data sheet that has dates in and data for Forecast and Actual so what I would need is the UDFs to only calculate for the dates selected
Example
01/01/2009 MAPE only for data that has dates of 01/01/2009
01/02/2009 MAPE only for data that has dates of 01/02/2009
So at least in my mind it would be like a MAPEIF formula to Only calculate the MAPE (ABS(Actual-Forecast)/Actual) a date is in another row same for the MAD and MSE
Not even sure if this is possible but hoping someone can help with it
Thanks in advance
Function MAPE(Actual As Range, Forecast As Range)
Dim i As Integer
Dim total1 As Single
total1 = 0
For i = 1 To Actual.Rows.Count
total1 = total1 + Abs(Actual.Cells(i, 1) - Forecast.Cells(i, 1)) / Actual.Cells(i, 1)
Next
MAPE = total1 * 100 / Actual.Rows.Count
End Function
Function MAD(Actual As Range, Forecast As Range)
Dim i As Integer
Dim total1 As Single
total1 = 0
For i = 1 To Actual.Rows.Count
total1 = total1 + Abs(Actual.Cells(i, 1) - Forecast.Cells(i, 1))
Next
MAD = total1 / Actual.Rows.Count
End Function
Function MSE(Actual As Range, Forecast As Range)
Dim i As Integer
Dim total1 As Single
total1 = 0
For i = 1 To Actual.Rows.Count
total1 = total1 + Abs(Actual.Cells(i, 1) - Forecast.Cells(i, 1)) ^ 2
Next
MSE = total1 / Actual.Rows.Count
End Function