Microsoft KB Archive/147266

= How to Solve for New X-Values Using INDEX and LINEST =

Article ID: 147266

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q147266





SUMMARY
This article contains information about 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.



MORE INFORMATION
In order to determine new x-values for a given set of data, you must solve for the following linear equation

y = mx + b

where y is the dependent y-value, m is the slope coefficient corresponding to each x-value, and b is a constant representing the y-intercept of the line.

You can solve this equation using the INDEX and LINEST functions.

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 the following formula:

x = ( y - b ) / m -or-

UnknownX = ( NewY - y-intercept ) / slope The following example illustrates how to determine a set of unknown x's using the formula shown above. Assume that you have the following table of known x- and y-values:

  A1:  Known x's   B1:  Known y's   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 slope and y-intercept values shown below:      D1: 5 D2: 90  You can now use these values to solve for each unknown x-value by using the formula (provided above) 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 they do not show decimal values.

  A1:  Known X's   B1:  Known Y's   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



Excel 97
For more information about the INDEX function, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel Help, type the following text

index

and then double-click the selected text to go to the "INDEX worksheet function" topic. If you are unable to find the information you need, ask the Office Assistant.

For more information about the LINEST function, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel Help, type the following text

linest

and then double-click the selected text to go to the "LINEST worksheet function" topic. If you are unable to find the information you need, ask the Office Assistant.

Excel 5.0
For more information about the INDEX function, click the Search button in Microsoft Excel Help and type the following text:

index function

For more information about the LINEST function, click the Search button in Microsoft Excel Help and type the following text:

linest function

Additional query words: y's TREND LOGEST GROWTH least squares line XL98 XL97 XL7 XL5 XL4 XL

Keywords: kbhowto kbualink97 KB147266

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.