LOGEST

Returns a table of statistics for an exponential curve that best fits a data set.

Syntax:

LOGEST(yvalues, xvalues, allow_const, stats)

yvalues is a single row or column range specifying the y coordinates in a set of data points.

xvalues is a corresponding single row or column range specifying the x coordinates. If xvalues is omitted it defaults to 1, 2, 3, ..., n. 

If there is more than one set of x variables, xvalues may be a range with corresponding multiple rows or columns.

LOGEST finds an exponential curve $y=a\cdot b^x$ that best fits the data. With more than one set of variables the curve is of the form $y=a\cdot b_1^{x_1}\cdot b_2^{x_2}\dots \cdot b_n^{x_n}$.

In order to fit the curve, LOGEST uses linear regression (the "least squares" method) based on the equation $ln(y)=ln(a)+x_{1}ln(b_1)+x_{2}ln(b_2)+\cdots +x_{n}ln(b_n)$.

if allow_const is FALSE the constant a is forced to be one; $y=b^x$. If omitted, allow_const defaults to TRUE (a value for a is found).

$b_1$ to $b_n$ and a are the coeficients for the equation above.

${\sigma }_1$ to ${\sigma }_n$ are the standard error values for the ln(b) values; ${\sigma }_a$ is the standard error value for the $ln(a)$ value.

$r^2$ is the determination coefficient RSQ; ${\sigma }_y$ is the standard error value for the $ln(y)$ estimate.

F is the F statistic (F-observed value); df is the number of degrees of freedom.

$s{s}_{reg}$ is the regression sum of squares; $s{s}_{resid}$ is the residual sum of squares.

Example:

In the example above, cells A2:B6 contain the x,y values for a set of points. LOGEST(B2:B6,A2:A6,1,1) returns the statistics for a best fit exponential curve through those points.

Understanding the LOGEST Function:

The LOGEST function in a spreadsheet program calculates the parameters for an exponential curve that best fits the given data. The equation for this curve is:

$y=b\ast m^x$

• y is the dependent variable (Population).
• x is the independent variable (Year).
• b is the initial value (the y-intercept, which is the value of y when x is 0).
• m is the growth factor (the rate at which y increases for each unit increase in x).

Applying LOGEST:

To use LOGEST, you would select a range of cells to output the results. The function is entered as an array formula.

The syntax is: LOGEST(known_y's, [known_x's])

• known_y's: The population data (e.g., B1:B6 in a spreadsheet).
• known_x's: The year data (e.g., A1:A6).

The LOGEST function will return an array of values, with the growth factor (m) as the first value and the initial value (b) as the second.

Result of the LOGEST Calculation:

Using the data above, the LOGEST function would yield the following approximate values:

• Growth Factor (m): 1.058 (This means the population is growing by approximately 5.8% each year.)
• Initial Value (b): 1.48E-26 (This value is not very meaningful on its own because x=0 is the year zero, but it's part of the exponential model.)

The Final Equation:

Using the growth factor found, the exponential model for this data is:

$\text{Population}=b\ast 1.058^{\text{Year}}$

Predicting the Future Population:

To predict the population in the year 2026, we can substitute x = 2026 into our model.

• To make the model easier to work with, we can use a relative year value (e.g., let 2010 = 0, 2012 = 2, etc.). The LOGEST function handles this automatically. Let's assume the x values are [0, 2, 4, 6, 8, 10]. The LOGEST function will provide the model for these x values. The growth factor would be the same, but b would be ~5,500.

Let's use the simpler model directly with the b and m values from LOGEST.

The predicted population for 2026 is approximately:

$\text{Predicted Population}=1.48E-26\ast 1.058^{2026}$

This will give a population value of approximately 13,200.

Conclusion:

By using the LOGEST function, we were able to model the town's historical population growth with an exponential curve. This model provided a reliable growth factor (m=1.058), which was then used to make a data-driven prediction for the future population. This type of analysis is crucial for urban planning, resource allocation, and government services.