Regression/Forecasting question?

[Jo]

Say I have in column A this:

23
34
56
27
18
32
24

What and how excel function would I use to predict the next value?

Thanks,
Jo

 

[Guest]

Forecast for a linear estimate

with your data in A1:A7

=FORECAST(8,A1:A7,{1,2,3,4,5,6,7})

gives me 25.

You can also look as Linest and Logest and Growth.



You can graph your data and fit a trend line of choice.

I graphed it and it looks pretty irregular to me.

Information on using LINEST for a polynomial fit:
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

 

[Jo]

Forecast for a linear estimate

with your data in A1:A7

=FORECAST(8,A1:A7,{1,2,3,4,5,6,7})

gives me 25.

You can also look as Linest and Logest and Growth.

You can graph your data and fit a trend line of choice.

I graphed it and it looks pretty irregular to me.

Information on using LINEST for a polynomial fit:http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy






- Show quoted text -

Could you please write down how LINEST, LONGEST formulas should be
written for the above example?

Thanks in advance Tom
Jo

 

[Guest]

=Linest(A1:A7,{1,2,3,4,5,6,7})

Array entered in the appropriate number of cells. this will give you the
coefficients of a linear trend line which you can use to make your pediction.
Not that the 1,2,...,7 are dummy X values assuming there is equal spacing
from an X axis perspective.

Best to look at the Excel HELP on LINESt and the link I supplied.

 

[Jo]

=Linest(A1:A7,{1,2,3,4,5,6,7})

Array entered in the appropriate number of cells. this will give you the
coefficients of a linear trend line which you can use to make your pediction.
Not that the 1,2,...,7 are dummy X values assuming there is equal spacing
from an X axis perspective.

Best to look at the Excel HELP on LINESt and the link I supplied.

--
Regards,
Tom Ogilvy







- Show quoted text -

Tom,

I applied LINESt as explained in your example above and worked fine
except on two values for X! Here is the results I got

1 2 3 4 5 6
$33.00 $33.00 $58.00 $67.00 $77.00 58.6
$33.00 $33.00 $44.00 $37.00 $31.00 6.6
$33.00 $33.00 $72.00 $86.00 $103.00 77.4
$33.00 $33.00 $59.00 $68.00 $82.00 71
$33.00 $33.00 $58.00 $67.00 $77.00 58.6
$33.00 $33.00 $62.00 $71.00 $82.00 60.2
$33.00 $33.00 $59.00 $68.00 $78.00 58.2
$33.00 $33.00 $43.00 $37.00 $31.00 7.4

Coulmn 6 is the y values.The 2nd and last rows results don't make
sense! Why?

I can email you the example if you prefere?

Thanks,
Mike

 

[Guest]

The relationship of this to your previous questions is unclear. Where do
these numbers come from?

Jerry

 

[Jo]

The relationship of this to your previous questions is unclear. Where do
these numbers come from?

Jerry










- Show quoted text -

These are just data. In each row, I am trying to do polynomial
regression analysis where the last column has the prediction; i.e. the
6th value.

For eaxmple, take the last row (33, 33, 43, 37, 31) and LINEST is
predicting the value 7.4 for the 6th outcome! For the row before last,
it is predicting 58.2 which makes sense. But why the last, for
example, is nonsense?!

 

[Guest]

What is the LINEST formula that you are using to produce the last column?

Jerry

 

[Jo]

What is the LINEST formula that you are using to produce the last column?

Jerry







- Show quoted text -

Here is the formula, for the each row, I am using:
LINEST(A2:E2,A1:E1^{1;2;3;4})

 

[Guest]

There are several problems here:

1. You are over-fitting your data, unless you know a priori that a quadratic
model is correct. While you can get a perfect fit to your training data by
fitting a polynomial of degree n-1 to n data points (assuming the x-values
are unique), such a model is typically poor for extrapolation or
interpolation (prediction). A useful way to assess model fit is "adjusted
r^2",
http://en.wikipedia.org/wiki/Coefficient_of_determination#Adjusted_R2
which includes a penalty for adding terms to the model that do not
adequately improve the predictive power

2. Your x-values are not unique. The first 2 values of the predictor
variable are the same, so you only have 4 unique x-values. This means that
you can fit no more than a cubic (which still might overfit the data [cf.
previous point]), since a quadratic model is singular (except for the
vagaries of numerical approximations due to finite precision representation
of floating point numbers that might prevent a given algorithm from
recognizing the model as singular).

3. If (as I previously understood), F2 is calculated as a polynomial in x=F1
from the output of LINEST, then I cannot reproduce your result. What version
of Excel are you using? Perhaps you are using less than full precision
values for the polynomial coefficients? I get a result around 43.58 using
either LINEST or TREND in either Excel 2003 or earlier.

Jerry

 

[Mike Middleton]

Jo -

You wrote: "For eaxmple, take the last row (33, 33, 43, 37, 31) and LINEST
is predicting the value 7.4 for the 6th outcome! For the row before last, it
is predicting 58.2 which makes sense. But why the last, for example, is
nonsense?!"

You may receive more enlightening replies if you describe the source of your
data, what you are trying to do, and why.

I question your rationale for using a polynomial of degree 4 to fit a curve
to 5 data points. When I do so with your data above, I obtain the following
coefficients:

Intercept 111.00000000000
X -150.16666666667
X^ 292.25000000000
X^3 -21.83333333333
X^ 41.75000000000

For X=6, the extrapolated value is 83.

I hesitate to call this extrapolated value a forecast or prediction.

If you plot the data using an XY (Scatter) chart, add a trendline Polynomial
of Order 4, on the trendline Options tab choose Forward Forecast of 1 unit,
and Display equation on chart, you'll see the same results.

I think it's always important to Look At The Data and to think about the
appropriate functional form before fitting a curve.

- Mike
http://www.MikeMiddleton.com

 

[Guest]

We agree that fitting a 4th degree polynomial to 5 data points is a bad idea
despite differing understanding of what Jo was doing.

I thought she said that there were 4 pairs of rows (an x row followed by a y
row). You thought she said that there were 8 rows of y data to be regressed
with x-data being the numbers 1 through 5 (which I took as simply column
labels). I now think that your understanding is probably what she intended,
but I still cannot reproduce either her results or your results. Your LINEST
formula then becomes
=LINEST(A9:E9,{1,2,3,4,5}^{1;2;3;4})
for which I get cubic and quintic coefficients of 92.25 and 1.75 instead of
the 292.25 and 41.75 that you report, although I do agree that
=TREND(A9:E9,{1,2,3,4,5}^{1;2;3;4},6^{1;2;3;4})
returns 83 instead of Jo's reported 7.4.

Jerry

 

[Mike Middleton]

Oops!

In my previous message below, some of the exponents for the variable labels
were shifted into the corresponding coefficients. It should be:

Intercept 111.00000000000
X -150.16666666667
X^2 92.25000000000
X^3 -21.83333333333
X^4 1.75000000000

- Mike

 

[Jo]

Jo -

You wrote: "For eaxmple, take the last row (33, 33, 43, 37, 31) and LINEST

is predicting the value 7.4 for the 6th outcome! For the row before last, it
is predicting 58.2 which makes sense. But why the last, for example, is
nonsense?!"

You may receive more enlightening replies if you describe the source of your
data, what you are trying to do, and why.

I question your rationale for using a polynomial of degree 4 to fit a curve
to 5 data points. When I do so with your data above, I obtain the following
coefficients:

Intercept 111.00000000000
X -150.16666666667
X^ 292.25000000000
X^3 -21.83333333333
X^ 41.75000000000

For X=6, the extrapolated value is 83.

I hesitate to call this extrapolated value a forecast or prediction.

If you plot the data using an XY (Scatter) chart, add a trendline Polynomial
of Order 4, on the trendline Options tab choose Forward Forecast of 1 unit,
and Display equation on chart, you'll see the same results.

I think it's always important to Look At The Data and to think about the
appropriate functional form before fitting a curve.

- Mikehttp://www.MikeMiddleton.com







- Show quoted text -

Mike,

Thanks, you reminded of something! Mathematically speaking, the LINEST
results I listed above are correct but two of the rsults above (2nd
and last of the far right col) are WRONG from a biz point of view! My
data above is about pricing. If a shirt price over 5 periods is: $10,
12, 17, 14, and 25, how can LINEST predicts that next time I should
sell for $1 or even -$1?!

So, can we say that regression analysis is non-sense for such
applications!? This is like saying: can regression analysis be used
for predicting stock price? No way...

So, what logic should be or can be used here, instead of reg?

 

[Mike Middleton]

Jo -

You wrote: > So, can we say that regression analysis is non-sense for such
applications!? ... So, what logic should be or can be used here, instead of
reg? <

As I wrote earlier: >> I think it's always important to Look At The Data and
to think about the appropriate functional form before fitting a curve. <<

You need to use your business knowledge to choose the appropriate model.
Regression is just a computational method. Instead of a "naive" model that
uses only time as the explanatory variable, you may need to include other
explanatory factors, e.g., competition, seasonality, etc. For more complex
models, regression may be very useful for sorting out the important factors.

- Mike
http://www.MikeMiddleton.com