The Excel LOGEST function returns statistical information on the exponential curve of best fit, through a supplied set of x- and y- values.
The basic statistical information returned is the array of constants, mn, mn-1, … , b (or the constants m and b if there is a single range of x-values), for the exponential curve equation. However, you can also request that additional regression statistics be returned.
Syntax:= LOGEST(known_y’s, known_x’s, const, stats)
The LOGEST function syntax has the following arguments:
-
known_y’s Required. The range or array of y values you already know in the relationship y = b*m^x. (From another perspective, a chart typically displays the same “y” values in the vertical axis.)
-
known_x’s Optional. The range or array of x values you typically know in the relationship y = b*m^x. (From another perspective, “x” values are frequently date serial numbers, counters, or other numeric categories.) If ‘known_x’s’ is omitted, it is assumed to be the array {1,2,3,…} that is the same size as ‘known_y’s’.
-
const Optional. A logical value that specifies whether to force the constant b to equal 1.
-
If const is TRUE or omitted, then b is calculated normally.
-
If const is FALSE, b is set equal to 1, and the m-values are fitted to y = m^x
-
-
stats Optional. A logical value that specifies whether to return additional regression statistics.
-
If stats is FALSE or omitted, LOGEST returns only the m-coefficients and the constant b, as shown in the first two rows of the following table.
-
If stats is TRUE, LOGEST returns these regression statistics as an array:
-
-
Example: Let’s look at some Excel LOGEST function examples and explore how to use the LOGEST function as a worksheet function in Microsoft Excel:
Example 1:
Cells A2-A12 and B2-B12 of the spreadsheet below list a number of known x and known y values, and also shows these points, plotted on a chart. Cells A16-B20 of the spreadsheet show the results of the Excel Logest function, which has been used to return statistical information relating to the exponential curve of best fit through these points.
As shown in the formula bar, the formula for the Logest function is:
Syntax: =LOGEST( B2:B12,A2:A12,TRUE,TRUE )
Result:
The curly brackets around this function show that it has been entered as an array formula.
Cells A16 and B16 give the values of the base, m as 1.025948807, and the y-intercept, b as 4.171774727. Therefore, the equation for the exponential curve of best fit through the given points is:
The remaining cells in the range A16-B20 give the following additional statistics for this curve:
- The standard error value for the base m is 0.00310866
- The standard error value for the constant b is 0.0986791
- The coefficient of determination is 0.882981321
- The standard error for the y estimate is 0.150622093
- The F statistic is 67.91079792
- The number of degrees of freedom is 9
- The regression sum of squares is 1.540693278
- The residual sum of squares is 0.204183133
Example 2:
Cells A2-A11, B2-B11 and C2-C11 of the spreadsheet below contain three different sets of independent variables (known x values), and cells D2-D11 of the spreadsheet contain the associated known y-values.
As shown in the formula bar, the formula for the Logest function in this case is:
Syntax: =LOGEST(D2:D11,A2:C11,TRUE,TRUE )
Result:
Cells A14-D18 of the spreadsheet show the results of the Excel Logest function, which has been used to return statistical information relating to the exponential curve of best fit through these points.
It is also seen, from the surrounding curly brackets, that the function has been entered as an array formula.
Cells A14-D14 give the values of the coefficents, m3, m2 and m1 as 2.010750937, 0.942167056 and 1.31373656, respectively and the y-intercept, b as 2.554652779. Therefore, the equation for the exponential curve of best fit through the given points is:
The remaining cells in the range A14-D18 give the following additional statistics for this curve:
- The standard error values for the coefficients m3, m2 and m1are 0.080315977, 0.012928031 and 0.04764347, respectively
- The standard error value for the constant b is 0.275195565
- The coefficient of determination is 0.997749047
- The standard error for the y estimate is 0.057701675
- The F statistic is 886.5125548
- The number of degrees of freedom is 6
- The regression sum of squares is 8.854886129
- The residual sum of squares is 0.0199769
and the unused cells show the #N/A error.
Notes:
- The equation for the curve is −y = b*m^xor
y = (b*(m1^x1)*(m2^x2)*_)
If there are multiple x-values, where the dependent y-value is a function of the independent x-values. The m-values are bases corresponding to each exponent xvalue, and b is a constant value. Note that y, x, and m can be vectors.
- The array that LOGEST returns is {mn,mn-1,…,m1,b}.
- The more a plot of your data resembles an exponential curve, the better the calculated line will fit your data. Both LINEST and LOGEST return an array of values that describes a relationship among the values, but LINEST fits a straight line to your data, whereas LOGEST fits an exponential curve.
- When you have only one independent x-variable, you can obtain y-intercept (b) values directly by using the following formula −Y-intercept (b): INDEX (LOGEST (known_y’s,known_x’s),2)
- You can use the y = b*m^x equation to predict future values of y, but the GROWTH Function does the same.
- When entering an array constant such as known_x’s as an argument, use commas to separate values in the same row and semicolons to separate rows. Separator characters may be different depending on your regional settings.
- You should note that the y-values predicted by the regression equation may not be valid if they are outside the range of y-values you used to determine the equation.
- If the array of known_x’s is not the same length as the array of known_y’s, LOGEST returns the #REF! error value.
- If any of the values in the supplied known_x’s or known_y’s arrays are not numeric (this may include text representations of numbers, as the LOGEST Function does not recognize these as numbers), LOGEST returns the #VALUE! error value.
- If either of the const or stats arguments cannot be evaluated to TRUE or FALSE, LOGEST returns the #VALUE! error value.
