Microsoft KB Archive/214251

= HOW TO: Use the INDEX and LINEST Functions to Solve for New X-Values in Excel 2000 =

Article ID: 214251

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214251



For a Microsoft Excel 98 and earlier version of this article, see 147266.

IN THIS TASK
SUMMARY
 * Solve the Linear Equation
 * Example

REFERENCES



SUMMARY
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.

back to the top

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

back to the top

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 To 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)

D2: =INDEX(LINEST(B2:B6,A2:A6),1,2)

These formulas return the following slope and y-intercept values:

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.

The worksheet should now be similar to the following table:

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 back to the top

