I am using MS Excel to do my data analysis. I have two dates. One date is my forecast date. For example, i am forecasting the stock to arrive - 06/09/2009. Another date is the actual stock arrival date - 06/10/2009.
I know that the date difference is 1 day. What I would like to calculate is the accuracy of my forecasting. If forecast date is same as actual arrival date then it should be 100%.
I am trying to find the function in excel to do this calculation. Is there any? Thanks.
If you are just working with dates and the accuracy is days then you can just subtract one day from the other.
For example if you had one column with expected date(in A1), one with actual date (in B1) then the next column could be difference (in C1).
=SUM(b1 - a1)
at the bottom of the C column, supposing you had 4 rows you would just do an average of the days
=AVERAGE(C1:C4)
that would give you the accuracy of your preditions.
Hope that helps
Generally
% accuracy = 100% - %abs(error) = 100% * [1 - abs(forecast - actual) / actual ]
Since the values of dates are not well-defined, you could normalize them by subtracting some initial date. For the above formula, this initial date will cancel out in the numerator, so you could just use:
100% * [1 - abs(forecast - actual) / (actual - initial)].
In Excel, the formula formatted for % would be: =1-ABS(C2-B2)/(B2-A2)
Using your example:
initial actual arrival forecast arrival % accuracy
01/01/09 06/10/09 06/09/09 99.38%