How to use the LOGEST function
What is the LOGEST function?
The LOGEST function returns an array of values representing the parameters of an exponential curve that fits your data, based on the "least squares" method. This function must be entered as an array formula.
Table of Contents
1. Introduction
What is an array formula?
An array formula is a formula that returns multiple values. In versions earlier than Excel 365 you have to follow these steps:
- Select the cell range that fits the output.
- Type or copy/paste the array formula in the formula bar.
- Press and hold CTRL and SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
The array formula in the formula bar changes so it also shows beginning and ending curly brackets like this: {=array_formula}
The curly brackets tell you that you successfully enter a formula as an array formula. The downside is that array formulas are easily broken when you edit the formula, you need to remember to repeat the steps above if you change the formula.
The good news is that Excel 365 simplified things a lot, you now enter regular and array formulas the same way simply by pressing the Enter key. Array formulas are now called dynamic array formulas meaning they automatically spill values to cells below and to the right as far as needed.
What is an exponential curve?
An exponential curve is a curve described by an exponential function of the form:
y = a * bx
a is the initial value when x = 0
b is the base (constant)
x is the independent variable
y is the dependent variable
How to fit data to an exponential curve using the "least squares" method?
The least squares method is a standard approach to fitting regression lines and curves to data by minimizing the sum of the squares of the residuals. It finds the "best" curve according to this squared error criterion.
What is the difference between the LOGEST and the LINEST function?
LINEST fits a straight line to your data whereas the LOGEST function fits an exponential curve.
2. LOGEST function Syntax
LOGEST(known_y's, [known_x's], [const], [stats])
3. LOGEST function Arguments
The equation for the curve is y = b*m^x or y = (b*(m1^x1)*(m2^x2)* ...) if multiple x-values are used. y, x and m can be vectors and LOGEST returns the following array: {mn, mn-1, ..., m1, b}. The m-values correspond to the x-values.
| known_y's | Required. Single column - Each row is a separate variable. Single row - Each column is a separate variable. |
| [known_x's] | Optional. Known x points, default values are 1, 2, 3, ... |
| [const] | Optional. A boolean value determining if constant b is equal to 0 (zero). TRUE - constant b is calculated. Default. FALSE - constant b is 0 (zero). |
| [stats] | Optional. A boolean value determining whether to calculate additional regression statistics. TRUE - Returns additional regression statistics. {mn, mn-1, ..., m1, b;sen, sen-1, ..., se1, seb;r2, sey;F, df;ssreg, ssresid} FALSE - returns only m and b. |
The following table shows what the LOGEST function returns if [stats] argument is TRUE.
| Statistic | Description |
| se1, se2, ..., sen | The standard error values. |
| seb | The standard error value for the constant b. seb returns #N/A when const argument is FALSE. |
| r2 | The coefficient of determination. A perfect correlation is 1 and 0 (zero) means no correlation based on comparing the actual and the LOGEST functions estimated y-values. |
| sey | The standard error for the estimated y-values. |
| F | The F statistic, or the F-observed value. Determines if the observed relationship between the dependent and independent variables occurs by chance. |
| df | The degrees of freedom assists you in finding F-critical values, then compare the values to the F statistic to get the confidence level for the model. |
| ssreg | The regression sum of squares. |
| ssresid | The residual sum of squares. |
What are standard error values?
Measure of accuracy of coefficient estimates. Lower standard error = more precise estimate.
What is coefficient of determination?
Proportion of response variation explained by model. Ranges 0 to 1. Higher is better fit.
What is F statistic or the F-observed value?
Ratio of model mean square to residual mean square. Tests overall fit. Higher F indicates more significance.
What are degrees of freedom?
Numbers of independent observations minus number of fitted coefficients. Used in F test.
What is the confidence level for the model?
Probability that coefficient confidence interval contains true value. 95% is commonly used. Based on standard error.
What is regression sum of squares?
Variation explained by the model. Should be large relative to residual sum of squares.
What is the residual sum of squares?
Variation NOT explained by the model. Should be small relative to regression SS.
4. LOGEST Function Example
A lab has recorded the number of bacteria in a culture every hour from the 4th hour to the 10th hour. How can the LOGEST function help determine the best fitted exponential curve? Here is the data:
| 0 | |
| 1 | |
| 2 | |
| 3 | |
| 4 | 15 |
| 5 | 20 |
| 6 | 27 |
| 7 | 40 |
| 8 | 59 |
| 9 | 91 |
| 10 | 129 |
The image above shows the data in cell range B19:C30. The array formula calculates the m and b coefficients for an exponential curve that best fits the given data points.
Array formula in cell range C16:D16:
The LOGEST function returns an m value of approx. 1.44 and a b value of 3.2. The chart above shows these data points as blue markers. The black line represents an exponential curve using the "least squares" method. Section 4.2 below demonstrates how this curve is calculated.
4.1 How to enter an array formula
Excel 365 subscribers can skip the following instructions.
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
4.2 How to calculate exponential curve based on m and b coefficients
Use the following formula to calculate y coordinates based on m and b coefficients calculated in cells C16:D16
Formula in cell E20:
The calculated data points in cells E20:E30 are the plotted exponential curve together with the "source data" shown as x markers on the chart.
Use the GROWTH function to predict future values.
Functions in 'Statistical' category
The LOGEST function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form