Use LINEST in WPS Spreadsheets to Fit Y = a/x + b
Question details
Calculate the coefficient a and intercept b for the equation Y = a/x + b by converting it into a linear regression problem.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has x and Y data and wants to fit the reciprocal equation Y = a/x + b with the LINEST function.
- Observed behavior
- The equation appears nonlinear when plotted against x, but it becomes linear when Y is plotted against 1/x.
Make sure every x value is nonzero, because 1/x cannot be calculated when x equals 0. Arrange the x values and corresponding Y values in matching rows before creating the transformed values.
Transform x to 1/x and use LINEST to return a and b
Use this method to fit Y = a/x + b when your data becomes approximately linear after x is converted to its reciprocal.
Rewrite the equation as Y = a(1/x) + b. In this linear form, LINEST returns a as the slope and b as the intercept when the 1/x values are used as the known x-values.
In WPS Spreadsheets, place the original x values in one column, such as A2:A10, and place the matching Y values in B2:B10.
In C2, enter =1/A2, then drag the fill handle down through the last data row. Column C now contains the transformed independent variable 1/x.
Select an empty cell and enter =INDEX(LINEST(B2:B10,C2:C10,TRUE,TRUE),1,1). The returned slope is coefficient a.
Select another empty cell and enter =INDEX(LINEST(B2:B10,C2:C10,TRUE,TRUE),1,2). The returned intercept is b.
Substitute the two results into Y = a/x + b. For example, if a is 12 and b is 3, the fitted equation is Y = 12/x + 3.
Check the reciprocal relationship with an XY scatter chart
Use a chart to confirm that linear regression is appropriate before relying on the fitted equation for estimates.
Calculate Y = a/x + b coefficients with LINEST in WPS Spreadsheets
WPS Spreadsheets lets you transform x values with a simple reciprocal formula and use LINEST to calculate the slope a and intercept b. You can also create an XY scatter chart to verify whether the transformed data supports a linear model.
- 1. Enter your source data: Open WPS Spreadsheets and enter x values in one column and their corresponding Y values in the next column.
- 2. Transform the independent variable: Enter =1/A2 in a helper column for the first x value, then fill the formula down for all records.
- 3. Return coefficient a: Use =INDEX(LINEST(Y_range,reciprocal_x_range,TRUE,TRUE),1,1) to return the slope.
- 4. Return intercept b: Use =INDEX(LINEST(Y_range,reciprocal_x_range,TRUE,TRUE),1,2) to return the intercept.
- 5. Validate the fit: Use Insert > Chart > XY (Scatter) to plot Y against the reciprocal x values and inspect the linearity.
Frequently Asked Questions
Why can LINEST fit Y = a/x + b even though the equation is nonlinear?
LINEST fits a straight-line relationship. After you transform x into 1/x, the equation becomes Y = a(1/x) + b, which is linear in the transformed variable.
Which LINEST result is a in Y = a/x + b?
When the known_x range contains 1/x values, the LINEST slope is a. You can return it with =INDEX(LINEST(Y_range,reciprocal_x_range,TRUE,TRUE),1,1).
How do I return b from LINEST in WPS Spreadsheets?
Use =INDEX(LINEST(Y_range,reciprocal_x_range,TRUE,TRUE),1,2). This returns the intercept b for the fitted equation.
Can I use x values equal to zero in this regression?
No. The transformation 1/x is undefined when x is zero. Remove, correct, or separately handle zero x values before using LINEST.
Should I use the fitted equation to predict values outside my data range?
Use extrapolation cautiously. Check an XY scatter chart of Y against 1/x first, and avoid predictions near x = 0 because reciprocal values change very rapidly there.
