4

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)

2 Answers 2

7

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.

2
  • 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, 2010 at 11:11
  • Please see my edit. Apr 1, 2010 at 11:38
4

In order to calculate Y upon X values in an Excel worksheet when you have a function with polynomial behavior put the y values in a column (says A), then put known X, X^2,... in sequential columns (i.e. B, C, etc.) and X's you want to calculate the corresponding values in other column (says Z) then use the following function: TREND(known_y's, [known_x's], [new_x's], [const]) in our example we may write this:

=TREND(A$, B$:C$, Z$, 1)

care about the trend function! that's mat type function means you must select output range and press ctrl+shift+enter after writing the formula in the first cell of range. get more information from Microsoft official site located below:

https://support.office.com/en-ca/article/TREND-function-db684fb1-9df0-46db-a2c9-fabb93011db1

hope to help

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .