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
Search JabSto ::

Custom Search