Article ID: 214251 - View products that this article applies to.
This article was previously published under Q214251
This step-by-step article explains how to determine values for a set of unknown x-values when you are given a set of known x-values and known y-values.
y = mx + bwhere y is the dependent y-value, m is the slope coefficient that corresponds to each x-value, and b is a constant that represents the y-intercept of the line.
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 ) / slope
To solve for the unknown x-values in cells A7:A10, follow these steps:
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: 135
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: 135
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.