Statistical Formular

[Eileen]

I would like to calculate the standard deviation of forecast vs. actual for
10 periods

Right now, I have to do step of step, (1) calculate deviation
(actual-forecast) (2) square deviation (3) average of the squares of the
deviation (4) square root of (3). I am looking for a Excel formular to
calcuate this...

I only found STDEV(number1,number2,...), a measure of how widely values are
dispersed from the average value. not the variation between forecast and
actual.

Please help !

 

[Dave F]

Have you looked at the statistical functions builtin in to Excel? If not,
click the f(x) icon immediately to the left of the formula window and look
under the Statistics category.

Dave

 

[Eileen]

Dave,

I only found Stdev.. to calculate the deviation off the average, not compare
the two array (forecast and actual). Did I miss anything ?

 

[Mike Middleton]

Eileen -

I am not aware of a single function for your task, but the SUMXMY2 worksheet
function will do most of the first three steps.

- Mike
http://www.mikemiddleton.com