Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If I have some data in an scatter plot in Excel, e.g.

X Y
1 10
2 20
3 30
4 40
5 50

and I want to find the Y value for X = 10, or X=3.5, or whatever (obviously this is a simplified example) I've been doing the following:

  • Add a trend-line to the scatter plot data
  • Format the trend-line to one that fits the data (linear in this case)
  • Display the equation for the trend-line on the chart
  • Type the equation into an empty cell, replacing x with a cell reference. E.g. "=10*A1" then put my X value into the cell A1

Is there a better way of doing this with Excel? It's quite a few steps, and fairly repetitive. Or maybe Excel is just a poor choice of application for doing this?

(I'm using Excel 2007)

share|improve this question

1 Answer 1

If you know about the model, which is linear in your case, then you can use the following formula to calculate Y values corresponding the X values you want. Let's say you want to calculate Y where X=3.5. Then you should use:

=3.5*LINEST(B2:B6,A2:A6)

where B2:B6 is the range for Y values, and A2:A6 is the range for X values.

Other functions that may be useful for you:

LOGEST(known_y's,known_x's,const,stats) 

In regression analysis, calculates an exponential curve that fits your data and returns an array of values that describes the curve. Because this function returns an array of values, it must be entered as an array formula.

GROWTH(known_y's,known_x's,new_x's,const)

Calculates predicted exponential growth by using existing data. GROWTH returns the y-values for a series of new x-values that you specify by using existing x-values and y-values. You can also use the GROWTH worksheet function to fit an exponential curve to existing x-values and y-values.

FORECAST(x,known_y's,known_x's) 

Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

share|improve this answer
    
Thanks, I didn't know about that function. However, the data I work with tends not to be linear - 2nd order polynomial is probably the most common. Do you know if there's a built in function to help in those cases? –  Wilka Apr 1 '10 at 11:11
    
Please see my edit. –  Mehper C. Palavuzlar Apr 1 '10 at 11:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.