Solve the Linear Equation
To determine new x-values for a given set of data, you must solve the following linear equation
y = mx + b
where 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
-or-
UnknownX = ( NewY - y-intercept ) / slope
Example
The 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: 135
- 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
D2: 90 - 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: 135