Finding Y with X on Excel scatter plot/trend line
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)
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:
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:
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.
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.
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.