HOW TO: Use the INDEX and LINEST Functions to Solve for New X-Values in Excel 2000
This article was previously published under Q214251
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Excel 98 and earlier version of this article, see 147266.
IN THIS TASK
This step-by-step article explains how to determine values for a setof unknown x-values when you are given a set of known x-values and knowny-values.
back to the top
Solve the Linear EquationTo determine new x-values for a given set of data, you must solve the following linear equation
y = mx + bwhere y is the dependent y-value, m is the slope coefficient that correspondsto each x-value, and b is a constant that represents the y-intercept of theline.
You can use the INDEX and LINEST functions to solve this equation.
The LINEST function is used to calculate the slope and the y-intercept values for the line, which are returned as a two-element array. The INDEX function allows you to retrieve these two values from the array and to use them to calculate one of the following formulas:
x = ( y - b ) / m
UnknownX = ( NewY - y-intercept ) / slopeback to the top
ExampleThe following example illustrates how to determine a set of unknown x-values by using the preceding formula. Assume that you have the following table of known x-values and y-values:
A1: Known x values B1: Known y values A2: 2 B2: 100 A3: 4 B3: 110 A4: 6 B4: 120 A5: 8 B5: 130 A6: 10 B6: 140 A7: B7: 95 A8: B8: 105 A9: B9: 115 A10: B10: 135To solve for the unknown x-values in cells A7:A10, follow these steps:
- In cells D1 and D2, type the following formulas:D1: =INDEX(LINEST(B2:B6,A2:A6),1,1)These formulas return the following slope and y-intercept values:
D2: =INDEX(LINEST(B2:B6,A2:A6),1,2)D1: 5
- You can now use these values to solve for each unknown x-value by using the preceding formula (x = [ y - b ] / m) to solve for x. To do this, type the following formula in cell A7:A7: =(B7-$D$2)/$D$1
- Copy this formula to cells A8:A10.
NOTE: You may need to format the cells so that they do not show decimal values.
A1: Known x values B1: Known y values A2: 2 B2: 100 A3: 4 B3: 110 A4: 6 B4: 120 A5: 8 B5: 130 A6: 10 B6: 140 A7: 1 B7: 95 A8: 3 B8: 105 A9: 5 B9: 115 A10: 9 B10: 135back to the top
For more information about the INDEX function, click Microsoft Excel Help on the Help menu, type index in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about the LINEST function, click Microsoft Excel Help on the Help menu, type linest in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
back to the top
y's TREND LOGEST GROWTH least squares line XL2000
Article ID: 214251 - Last Review: 12/05/2015 12:31:27 - Revision: 3.1
Microsoft Excel 2000 Standard Edition
- kbnosurvey kbarchive kbhowto kbhowtomaster KB214251