Microsoft Office Tutorials and References

In Depth Information

**Syntax:**

Syntax:

=STDEV.p(number1,number2,...)

The STDEV.P function calculates standard deviation based on the entire pop-

ulation, given as arguments. The standard deviation is a measure of how

widely values are dispersed from the average value (that is, the mean).

STDEV.P assumes that its arguments are the entire population. If your data

represents a sample of the population, you can compute the standard devi-

ation by using STDEV.S. For large sample sizes, STDEV.S and STDEV.P return

approximately equal values. The standard deviation is calculated using the

“
biased
”
or
“
n
”
method.

Prior to Excel 2007, VAR.S was simply known as VAR. VAR.P was known as

VARP. STDEV.S was STDEV. STDEV.P was STDEVP. If you are going to be sharing

your workbook with people using Excel 2003 or earlier, use the old names in-

stead of the new names.

The arguments number1, number2, ... are one to 255 arguments for which you

want the average of the absolute deviations. You can also use a single ar-

ray or a reference to an array instead of arguments separated by commas.

The arguments must be either numbers or names, arrays, or references that

contain numbers. If an array or a reference argument contains text, logical

values, or empty cells, those values are ignored; however, cells that con-

tain the value 0 are included.

Examples of Functions for Regression and Forecasting

Regression analysis allows you to predict the future, based on past events.

Suppose you have observed total sales for the past several years. Regres-

sion analysis finds a line that best fits the past data points. You can then

use the description of that line to predict results for the future data points.

Regression works by finding a line that can best be drawn through existing

data points. In real-life data, the data points aren
’
t arranged exactly in a

line. Any line that the computer draws will have errors at any data point.

Regression finds the line that minimizes the errors at each data point.

Consider the error in a regression line. The actual data point in Year 1 might

be higher than the regression line by 2. In Year 2, the data might be lower by

1, and in Year 3 it might be lower by 1. If you added up these three errors, you

would have an error of 0. This is a bad method. If you used this method to