Use LOGEST to Calculate Exponential Trendline Values in WPS Office Spreadsheets
Question details
The user needs to calculate exponential trendline values in worksheet cells and convert LOGEST coefficients to match the Excel chart exponential trendline equation.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- An Excel chart displays an exponential trendline as y = B*EXP(M*x), while LOGEST returns coefficients for y = B*N^x; the user wants to reproduce the trendline values in cells using LOGEST.
- Observed behavior
- The LOGEST function returns a base N and coefficient B instead of the chart's exponential coefficient M, so the results must be converted with M=LN(N) and N=EXP(M) to match the chart trendline.
Check that the known_y values are all positive and that the known_x values are numeric before using LOGEST, because exponential regression is undefined for zero or negative y-values.
Calculate Exponential Trendline Values by Converting LOGEST Coefficients
Use this when you want to reproduce an Excel chart exponential trendline in worksheet cells, converting the LOGEST base N to the exponential coefficient M.
The Excel chart exponential trendline uses y = B*EXP(M*x). LOGEST returns coefficients for y = B*N^x, so the two formulas are equivalent when M = LN(N) and N = EXP(M).
This conversion lets you use dynamic worksheet cells instead of reading values visually from a chart trendline label.
In WPS Office Spreadsheets, identify a column or row of positive known_y values and matching numeric known_x values, such as B2:B10 and A2:A10.
Select a horizontal 1x2 range, enter =LOGEST(B2:B10,A2:A10,TRUE,FALSE), and press Ctrl+Shift+Enter for a legacy array formula, or Enter if your version uses dynamic arrays.
Use the first returned value as N, the base in y = B*N^x, and the second returned value as B, the constant multiplier.
In a helper cell enter =LN(N_cell) to calculate M, because the chart trendline exponential coefficient is M = LN(N).
In the first output cell enter =B_cell*EXP(M_cell*A2) to match the Excel chart's y = B*EXP(M*x) trendline, or use =B_cell*N_cell^A2 to keep the LOGEST base form.
Use LOGEST and Excel-Compatible Formulas in WPS Office Spreadsheets
WPS Office Spreadsheets supports LOGEST, LN, and EXP exactly the way Excel does, so you can calculate exponential trendline coefficients, convert the base, and generate predicted values in cells without leaving the familiar spreadsheet workflow.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, click Sheets, and open the .xlsx workbook that contains the chart and data.
- 2. Use LOGEST on your data: Select a horizontal range, enter =LOGEST(known_y's, known_x's, TRUE, FALSE), and accept the array return with Enter or Ctrl+Shift+Enter depending on your WPS version.
- 3. Convert the base with LN: Enter =LN(N_cell) to get the exponential coefficient M used by the chart trendline equation.
- 4. Apply the predicted-value formula: Enter =B_cell*EXP(M_cell*first_x_cell) to generate worksheet trendline values that match the Excel chart.
Frequently Asked Questions
What is the difference between the Excel exponential trendline equation and the LOGEST formula?
Excel chart exponential trendlines display y = B*EXP(M*x), while LOGEST returns y = B*N^x. Both are equivalent; convert with M=LN(N) and N=EXP(M).
How do I enter LOGEST as an array formula in WPS Office Spreadsheets?
Select a horizontal 1x2 range, type =LOGEST(known_y's, known_x's, TRUE, FALSE), and press Ctrl+Shift+Enter if your version requires legacy array entry. In dynamic array versions, just press Enter.
Why does my LOGEST formula return #NUM!?
Exponential regression requires positive known_y values. Check for zero or negative y-values and make sure the x and y ranges have the same number of rows or columns.
Can I use the LOGEST base directly without converting with LN?
Yes. Use =B*N^x with the base N returned by LOGEST. If you need the chart's EXP form, use M=LN(N) and then =B*EXP(M*x).
Does LOGEST work in WPS Office the same as in Microsoft Excel?
Yes. WPS Office Spreadsheets supports LOGEST with the same arguments and array behavior, and it can open .xlsx workbooks without changing these formulas.
