Hello, what is the formula to calculate variance in Excel between the actual data and the forecasted one? Many thanks!
Hello, what is the formula to calculate variance in Excel between the actual data and the forecasted one? Many thanks!
When calculating simple averages, "variance" refers to a quantity calculated by summing the squares of the deviations from the mean and dividing by the number of points. This value is given the greek symbol sigma^2: sigma^2=sum[(xmeasured-xaverage)^2]/n.
I don't know that regression uses the term variance to describe a speicific statistic. Deviation is still used (xmeasured-xcalculated). I often use a relative deviation [(xmeasured-xcalculated)/xmeas]. One could use the sum of the squares of these values to get an overall feel for how well the correlating equation fits the measured data. There is also the correlation coefficient r, which gives a feel for how well the data are correlated, and I expect there is a built in Excel function for this statistic, or it can be output as part of the additional statistics output by the LINEST function. Perhaps one of these descriptors is what you are after.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks