Microsoft Office Tutorials and References

In Depth Information

**Understanding linear and exponential regression**

Assuming that the test scores in the example are normally distributed, you can deduce that

about 68 percent of the students (the general-rule percentage) achieved scores from 83.65

(the average 89.20 minus the standard deviation 5.55) to 94.75 (89.20 plus 5.55).

You can also use the AGGREGATE function to apply the VAR.S function with options to

ignore hidden rows and error values. For more information, see “The AGGREGATE function”

in Chapter 14.

Calculating total population statistics: VAR.P and STDEV.P

If the numbers you’re analyzing represent an entire population rather than a sample,

use the VAR.P and STDEV.P functions to calculate variance and standard deviation. These

functions take the arguments
number1
,
number2
, and so on, and they accept up to 255

arguments.

Assuming that cells B4:E8 in the worksheet shown in Figure 17-2 represent the total

population, you can calculate the variance and standard deviation with the formulas

=VAR.P(B4:E8) and =STDEV.P(B4:E8). The VAR.P function returns 29.26, and the STDEV.P

function returns 5.41.

Note

The STDEV.S, STDEV.P, VAR.S, and VAR.P functions do not include text values or

logical values in their calculations. If you want to include these values, use the A versions:

STDEVA, STDEVPA, VARA, and VARPA. For more information, see “The A functions”

earlier in this chapter.

You can also use the AGGREGATE function to apply these functions with options to ignore

hidden rows and error values. For more information, see “The AGGREGATE function” in

Chapter 14.

Understanding linear and exponential regression

Excel includes several array functions for performing linear regression (LINEST, TREND,

FORECAST, SLOPE, and STEYX) and for performing exponential regression (LOGEST and

GROWTH). You enter these functions as array formulas, and they produce array results. You

can use each of these functions with one or several independent variables. The following

list defines the different types of regression:

Linear regression
Produces the slope of a line that best its a single set of data.

Based on a year’s worth of sales figures, for example, linear regression can tell you

●