0

I have posted this question, not sure how to move that question to this stats.stackexchange.com. https://stackoverflow.com/questions/28702634/predictive-accuracy-formula-in-excel-or-r?noredirect=1#comment45695509_28702634

Recently, I have built a model and I have the output similar to this below. The output is in the Excel. I am trying to understand if prediction error can be calculated in the Excel? For example, could I write a formula (RMSE, SSE, MSE) in excel to determine the predictive accuracy for the table below?

Basically, can I calculate the prediction error for the predicted sales given actual sales? pred_minus_acutal and (predicted - actual)/actual are just scenarios I tried. That may not be right way to get the prediction error.

I am not sure if these ideas are right either. Thanks!

customer id predicted_sales actual_sales 1A 100 150 2A 200 100 3A 300 256 1B 100 300 4B 400 390 6B 500 502

Community
  • 1
sharp
  • 191
  • 2
  • 9
  • Is this helpful: http://stats.stackexchange.com/questions/131267/weka-result-interpretation/131273#131273 ? – Tim Feb 24 '15 at 18:12

2 Answers2

1

I think that you could use Excel's Regression standard dialog for prediction (make sure that Residuals item is checked). In that case, Excel should provide you with an output, which will contain predictive accuracy (column Residuals in RESIDUAL OUTPUT section). Please see this page for details, an example and explanation. @Tim's answer, linked in his comment above, is also useful in interpreting the results - in conjunction with a regression analysis, performed prior to that.

Aleksandr Blekh
  • 7,867
  • 2
  • 27
  • 93
1

yes you can easily duplicate any of the formulas in Excel.

RMSE is simple enough. Make another column that is the difference between observed and predicted values.

Then do something like =SQRT(SUMSQ(C2:C100)/COUNT(C2:C100)) which is the formula for RMSE. (assuming C is your list of differentials between predicted and observed).

Something like MAPE is even easier - that can be calculated on a row by row basis.

abs(actual - observed)/actual

Then average that column. Yeah, you can pretty much look-up and copy any formula into Excel. I've replicated a lot of R code in Excel (like ETS models) simply because it's easier to pass along.

user45867
  • 241
  • 2
  • 6
  • correct me if I am wrong. RMSE is done for the whole data set, not for each customer right? If I were calculate the prediction error for each customer, what should I be doing. Is that even possible to make prediction error for each customer? – sharp Feb 24 '15 at 19:24
  • Yes that's right, it's done in aggregate for the whole set ... but you can do for one customer if you have a time series for that particular customer as well. Usually forecasts are done on an ongoing basis. MAPE is a bit easier to understand in that you really can do it at the individual level, but what metric you use depends on how prediction errors effect your business. – John Babson Feb 25 '15 at 23:46