Microsoft Office Tutorials and References

In Depth Information

**Performing an Exponential Regression**

•
known_y's

known_y's
—
This is the set of y values you already know in the re-

lationship y = b × m^x. If the array known_y'sis in a single column,

each column of known_x'sis interpreted as a separate variable. If

the array known_y'sis in a single row, each row of known_x'sis in-

terpreted as a separate variable.

•
known_x's

known_x's
—
This is an optional set of x values you might already

know in the relationship y = b × m^x. The array known_x'scan include

one or more sets of variables. If only one variable is used,

known_y'sand known_x'scan be ranges of any shape, as long as they

have equal dimensions. If more than one variable is used, known_y's

must be a range of cells with a height of one row or a width of one

column (which is also known as a vector). If known_x'sis omitted, it is

assumed to be the array {1,2,3,...} that is the same size as known_y's.

•
const

const
—
This is a logical value that specifies whether to force the

constant b to equal 1. If constis TRUE or omitted, b is calculated nor-

mally. If constis FALSE, b is set equal to 1, and the m values are

fitted to y = m^x.

•
stats

stats
—
This is a logical value that specifies whether to return ad-

ditional regression statistics. If statsis TRUE, LOGEST returns the

additional regression statistics (refer to
Figure 14.16
)
, so the re-

turned array is {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;

F,df;ssreg,ssresid}. If statsis FALSE or omitted, LOGEST returns

only the m coefficients and the constant b.

The more a plot of data resembles an exponential curve, the better the cal-

culated line fits the data. Like LINEST, LOGEST returns an array of values

that describes a relationship among the values, but LINEST fits a straight

line to the data; LOGEST fits an exponential curve.

Performing an Exponential Regression

Figure 14.19
shows an estimated population in column B and the generation in

column A. To perform an exponential regression, follow these steps:

11. Because there is one independent variable, the results from the re-

gression occupy two columns, so find a blank range of the spread-

sheet and select a range that is two columns wide by five rows tall,

such as E2:F6.