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

Last reviewed: February 2, 1998
Article ID: Q147266

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

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, perform the following 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

REFERENCES

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
Keywords : xlformula kbualink97
Version : WINDOWS:3.0,4.0,5.0,7.0,97; MACINTOSH:3.0,4.0,5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.