Predict Y-values on new X-values based on other actual X and Y values?

  • Thread starter Thread starter NorTor
  • Start date Start date
N

NorTor

Good morning all!

I need to generate (rather extend) a graph-line with predicted values
(Y) for new X-values. I do have a range with actual X and Y values to
base the predictions on.

Actual ranges are as follows:

X (months):
3 6 9 12 15 18 21 24 27
30 33 36 39 42 45 48 51 54
57 60

Y (Relative gains, accumulated):
5,5 % 11,0 % 17,1 % 22,1 % 26,5 % 30,4 % 33,7 % 36,4 % 38,7 %
40,7 % 42,4 % 43,8 % 45,0 % 46,1 % 47,1 % 48,1 % 49,0 % 49,8 %
50,4 % 50,8 %

X values are months, and I need to predict Y-data for months 63 to 120
(in 3 months steps, like for the first 60 months.

Btw, I also have the non-accumulated values that can be used for
Y-values, if this makes it easier.

I guess a keyword here is 'trend', but I cannot get it to work.

Please, please, pretty please, help me :-)


Best regards,
NorTor
 
you line isn't linear since you have a declining rate of change. Why not go
an xy-scatter plot of your data and fit a trend line to it. You can
examine the non-linear forms. Make the formula visible and increase the
number of decimal points shown. this will give you an idea of the formula
you need to make the prediction.
 
Dear Tom!

Thank you so much for your reply - I now have a graphical trendline
(logarithmic - custom) and the equation is displayed on the chart area
with 4 decimals.

What I have to sort out now, is how to make a table displaying the X
values (0 to 120 in steps of 3, eg 0, 3, 6, ... ,120) and the
corresponding predicted Y-values based on the trendline equation.

Is there a way to get the trendline equation into a cell and refer to
a cell with the X-value to use? Using VBA maybe?

Hope to hear from you again!


My best regards,
NorTor
 
Back
Top