Adding Criteria to UDF functions

[mduff]

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

[MaximS]

First one:




Function MAPE(Actual As Range, Forecast As Range, Dates As Range, Criteria As Range)




Dim i As Integer

Dim total1 As Single

total1 = 0


For i = 1 To Actual.Rows.Count
If Dates.Cells(i, 1).Value = Criteria Then
total1 = total1 + Abs(Actual.Cells(i, 1) _
- Forecast.Cells(i, 1)) / Actual.Cells(i, 1)
End If
Next


MAPE = total1 * 100 / Actual.Rows.Count
End Function






and the rest can be converted in exact same way

[mduff]

that is very cool and works like a charm thanks so much (you guys rock):bow:



Now I have one more issue in some of my data I have an Actual of 0 but a forcast of a number on these days it is giveing me a #VALUE! error any error trapping or something I can add to aviod this error
:dunno

[MaximS]

try:



Function MAPE(Actual As Range, Forecast As Range, Dates As Range, Criteria As Range)
Dim i As Integer
Dim total1, calc As Single
Dim DateCount As Single

total1 = 0

For i = 1 To Actual.Rows.Count
If Dates.Cells(i, 1).Value = Criteria Then
On Error Resume Next
calc = Abs(Actual.Cells(i, 1) _
- Forecast.Cells(i, 1)) / Actual.Cells(i, 1)
On Error GoTo 0
If Not IsError(calc) Then total1 = total1 + calc
End If
Next

DateCount = WorksheetFunction.CountIf(Dates, Criteria)
'Posiblly that should be
MAPE = total1 * 100 / DateCount
'MAPE = total1 * 100 / Actual.Rows.Count
End Function

[Bob Phillips]

I think the function is wrong in using Actual.Rows.Count



Function MAPE(Actual As Range, Forecast As Range, Dates As Range, Criteria As Range)
Dim i As Long
Dim total1 As Double
Dim numInc As Long

total1 = 0
For i = 1 To Actual.Rows.Count

If Dates.Cells(i, 1).Value = Criteria Then

If Actual.Cells(i, 1) <> 0 Then

total1 = total1 + Abs(Actual.Cells(i, 1) _
- Forecast.Cells(i, 1)) / Actual.Cells(i, 1)
numInc = numInc + 1
End If
End If
Next

If numInc > 0 Then MAPE = total1 * 100 / numInc
End Function

[Bob Phillips]

You can also do it by formula

=SUM(IF((C1:C3=D1)*(A1:A3<>0),ABS(B1:B3-A1:A3)/A1:A3))*100
/SUM((C1:C3=D1)*(A1:A3<>0))

[mduff]

thanks so much it working well now
I only tired the UDF on the formula is that an Array (Control Shift Enter)?

[Bob Phillips]

thanks so much it working well now
I only tired the UDF on the formula is that an Array (Control Shift Enter)?

Yes it is.