alexaronson
Active Member
- Joined
- Sep 30, 2005
- Messages
- 314
Hello,
I have a pivot table which I have created a calculated field called WMAPE which stands for Weighted Mean Absolute Precent Error. The formula looks like this.
=IF(AND(SUM(Actual)=0,SUM(Forecast)=0),0,IF( SUM(Actual)=0,100,'Abs Error'/Actual))
The first IF is checking to see if a forecast and actual existed, if both values = 0, then WMAPE = 0 since there is no error.
The second if is going to check to make sure if Actual equal 0 than WMAPE is 100 because the user forecasted a value, but nothing shipped.
Else, if neither condition exist, WMAPE is Abs Error / Actual.
It turns out that some of my raw data for Actual contains negative numbers which is causing my WMAPE to return a negative number which is not correct.
I would like to edit the calculated firled to add another IF statement to fix my negative problem. However, I cannot find out how to do that?
Any suggestions?
I have a pivot table which I have created a calculated field called WMAPE which stands for Weighted Mean Absolute Precent Error. The formula looks like this.
=IF(AND(SUM(Actual)=0,SUM(Forecast)=0),0,IF( SUM(Actual)=0,100,'Abs Error'/Actual))
The first IF is checking to see if a forecast and actual existed, if both values = 0, then WMAPE = 0 since there is no error.
The second if is going to check to make sure if Actual equal 0 than WMAPE is 100 because the user forecasted a value, but nothing shipped.
Else, if neither condition exist, WMAPE is Abs Error / Actual.
It turns out that some of my raw data for Actual contains negative numbers which is causing my WMAPE to return a negative number which is not correct.
I would like to edit the calculated firled to add another IF statement to fix my negative problem. However, I cannot find out how to do that?
Any suggestions?