How to Use Excel LOGEST and GROWTH for Exponential Regression
Question details
The user needs to calculate exponential regression coefficients using the LOGEST function and generate accurate predicted values for new x-values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying exponential regression to a dataset to calculate coefficients and predict future y-values based on new x-values.
- Observed behavior
- The results calculated using the LOGEST function's model (y = b*m^x) may appear unmatched with the chart's exponential trendline model (y = b*EXP(m2*x)) when generating predicted values.
Ensure your dataset is organized into two separate columns or rows for known x-values and known y-values, with no blank cells or text values in the range.
Use the GROWTH Function for Predicted Values
Use the GROWTH function to directly calculate predicted values based on exponential regression without manually converting LOGEST coefficients.
Excel's LOGEST models data using the equation y = b*m^x. However, an exponential chart trendline uses the equation y = b*EXP(m2*x). Because of this difference, the coefficients might look incorrect, but they are mathematically related by m2 = LN(m) and m = EXP(m2).
Instead of manually extracting LOGEST coefficients and converting them, the most accurate and efficient way to calculate predicted y-values is by using the GROWTH function.
Click on the cell where you want the predicted y-value to appear.
Type `=GROWTH(known_y's, known_x's, new_x's)`. For example, `=GROWTH(B2:B7, A2:A7, D2:D6)` where B2:B7 contains your original y-values, A2:A7 contains original x-values, and D2:D6 contains the new x-values you want to predict.
If you need to force the y-intercept (the 'b' constant) to exactly 1, add FALSE as the fourth argument. For example: `=GROWTH($B$3:$B$8, $A$3:$A$8, A3, FALSE)`.
Press Enter. If you are calculating multiple predictions in adjacent cells, use absolute references for your known data (like $B$3:$B$8) and drag the fill handle down to apply the formula.
Easily Perform Exponential Regression in WPS Spreadsheet
WPS Spreadsheet provides full support for advanced statistical arrays, including LOGEST and GROWTH. You can perform complex exponential regression, calculate trendlines, and forecast data exactly as you do in Microsoft Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your regression data.
- 2. Select the target cell: Click on the cell where you wish to calculate your predicted values or coefficients.
- 3. Apply the GROWTH function: Type the formula `=GROWTH(known_ys, known_xs, new_xs)` and press Enter to generate the predicted values.
- 4. Visualize the trendline: Select your data, insert a Scatter Chart, click on Chart Elements, and check 'Trendline' to visually compare your exponential regression.
Frequently Asked Questions
Why do the coefficients from LOGEST differ from my chart's exponential trendline?
LOGEST calculates coefficients for the model y = b*m^x, whereas the chart trendline uses the model y = b*EXP(m2*x). The models are mathematically equivalent. You can convert between them using the formulas m = EXP(m2) or m2 = LN(m).
What does the FALSE argument do in the GROWTH or LOGEST function?
Using FALSE in the fourth argument (the 'const' argument) of the GROWTH or LOGEST function forces the y-intercept (the 'b' variable in the equation) to equal exactly 1.
How do I enter LOGEST to see all regression statistics?
To view the full array of regression statistics, select a range of cells (typically 5 rows by 2 columns), type your LOGEST formula adding TRUE for the stats argument, and press Ctrl+Shift+Enter to submit it as an array formula.
