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

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
 
T

Tom Ogilvy

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.
 
N

NorTor

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
 
Top