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

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/E...Polynomial.htm

--
Regards,
Tom Ogilvy




--
Regards,
Tom Ogilvy


"Jo" wrote:

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

=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


"Jo" wrote:

On Aug 2, 11:44 am, Tom Ogilvy
wrote:
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/E...Polynomial.htm

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy



"Jo" wrote:
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- Hide quoted text -

- Show quoted text -

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

Thanks in advance Tom
Jo

On Aug 2, 3:08 pm, Tom Ogilvy
wrote:
=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



"Jo" wrote:
On Aug 2, 11:44 am, Tom Ogilvy
wrote:
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/E...Polynomial.htm

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"Jo" wrote:
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- Hide quoted text -

- Show quoted text -

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

Thanks in advance Tom
Jo- Hide quoted text -

- 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

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

Jerry

"Jo" wrote:

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

On Aug 9, 7:42 am, Jerry W. Lewis wrote:
The relationship of this to your previous questions is unclear. Where do
these numbers come from?

Jerry



"Jo" wrote:

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- Hide quoted text -

- 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?!

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

Jerry

"Jo" wrote:

On Aug 9, 7:42 am, Jerry W. Lewis wrote:
The relationship of this to your previous questions is unclear. Where do
these numbers come from?

Jerry



"Jo" wrote:

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- Hide quoted text -

- 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?!

On Aug 9, 11:34 pm, Jerry W. Lewis wrote:
What is the LINEST formula that you are using to produce the last column?

Jerry



"Jo" wrote:
On Aug 9, 7:42 am, Jerry W. Lewis wrote:
The relationship of this to your previous questions is unclear. Where do
these numbers come from?

Jerry

"Jo" wrote:

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- Hide quoted text -

- 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?!- Hide quoted text -

- Show quoted text -

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

There are several problems he

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/Coeffic...on#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

"Jo" wrote:

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

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


"Jo" wrote in message
oups.com...
On Aug 9, 11:34 pm, Jerry W. Lewis wrote:
What is the LINEST formula that you are using to produce the last column?

Jerry



"Jo" wrote:
On Aug 9, 7:42 am, Jerry W. Lewis wrote:
The relationship of this to your previous questions is unclear.
Where do
these numbers come from?

Jerry

"Jo" wrote:

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- Hide quoted text -

- 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?!- Hide quoted text -

- Show quoted text -

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

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" wrote:

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


"Jo" wrote in message
oups.com...
On Aug 9, 11:34 pm, Jerry W. Lewis wrote:
What is the LINEST formula that you are using to produce the last column?

Jerry



"Jo" wrote:
On Aug 9, 7:42 am, Jerry W. Lewis wrote:
The relationship of this to your previous questions is unclear.
Where do
these numbers come from?

Jerry

"Jo" wrote:

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- Hide quoted text -

- 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?!- Hide quoted text -

- Show quoted text -

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

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


"Mike Middleton" wrote in message
...
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


"Jo" wrote in message
oups.com...
On Aug 9, 11:34 pm, Jerry W. Lewis wrote:
What is the LINEST formula that you are using to produce the last
column?

Jerry



"Jo" wrote:
On Aug 9, 7:42 am, Jerry W. Lewis wrote:
The relationship of this to your previous questions is unclear.
Where do
these numbers come from?

Jerry

"Jo" wrote:

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- Hide quoted text -

- 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?!- Hide quoted text -

- Show quoted text -

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

On Aug 13, 3:51 pm, "Mike Middleton" wrote:
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

"Jo" wrote in message

oups.com...



On Aug 9, 11:34 pm, Jerry W. Lewis wrote:
What is the LINEST formula that you are using to produce the last column?

Jerry

"Jo" wrote:
On Aug 9, 7:42 am, Jerry W. Lewis wrote:
The relationship of this to your previous questions is unclear.
Where do
these numbers come from?

Jerry

"Jo" wrote:

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- Hide quoted text -

- 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?!- Hide quoted text -

- Show quoted text -

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

- 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?

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