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

Article translations Article translations
Article ID: 214251 - View products that this article applies to.
This article was previously published under Q214251
Expand all | Collapse all

On This Page

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.

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
				
To solve for the unknown x-values in cells A7:A10, follow these steps:
  1. 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
  2. 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
  3. 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
				

REFERENCES

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.

Properties

Article ID: 214251 - Last Review: September 27, 2003 - Revision: 3.1
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbhowto kbhowtomaster KB214251

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com