Lucas in London
Board Regular
- Joined
- Jun 17, 2002
- Messages
- 88
Hi there,
I'm trying to write a function in excel which will calculate the mean absolute percentage error (MAPE) for a variable. Basically this is a measure of forecast accuracy, which compares forecasts for a variable against actual values.
For those not familiar with this measure, basically I need a function that will calculate the absolute percentage difference between the values for two variables and return the average or sum of these:
For example say we have two variables (X and Y) with following values:
X Y The pct difference =
7 7 0
10 11 10
15 21 40
Average = 16.6
MAPE = 16.6
I know how to do these calculations within a spreadsheet but I want to do using a custom function. Also I know how to create inputboxes for the user to select the ranges for X and Y but I just haven't got a clue how to write the actual basic formula to calculate differences in values between two arrays and calculate statistics on these.
I hope someone out there can help me.
Many Thanks
Lucas in currently a sunny London
I'm trying to write a function in excel which will calculate the mean absolute percentage error (MAPE) for a variable. Basically this is a measure of forecast accuracy, which compares forecasts for a variable against actual values.
For those not familiar with this measure, basically I need a function that will calculate the absolute percentage difference between the values for two variables and return the average or sum of these:
For example say we have two variables (X and Y) with following values:
X Y The pct difference =
7 7 0
10 11 10
15 21 40
Average = 16.6
MAPE = 16.6
I know how to do these calculations within a spreadsheet but I want to do using a custom function. Also I know how to create inputboxes for the user to select the ranges for X and Y but I just haven't got a clue how to write the actual basic formula to calculate differences in values between two arrays and calculate statistics on these.
I hope someone out there can help me.
Many Thanks
Lucas in currently a sunny London