I am trying to €˜FORECAST or €˜TREND a series and neither function appears to
be working as I would expect. For example, I have the following series:
1,2,3,1,2,3,1,2,3,1,2,3 and I want to FORECAST or TREND the next 5 periods.

When I use the FORECAST (=FORECAST(M$1,$A10:L10,$A$1:L$1)) function, I get:
2.4, 2.4, 2.5, 2.5, 2.6

When I use the TREND (=TREND($A4:L4,$A$1:L$1)) Function, I get: 1.7, 2.2,
2.2, 2.3, 2.3

I would expect to get 1,2,3,1,2, for both functions.

Anyone know what I am doing wrong or if I should be using another function.

Thanks
--
Mike

Mike wrote:
I am trying to €˜FORECAST or €˜TREND a series and neither function appears to
be working as I would expect. For example, I have the following series:
1,2,3,1,2,3,1,2,3,1,2,3 and I want to FORECAST or TREND the next 5 periods.

When I use the FORECAST (=FORECAST(M$1,$A10:L10,$A$1:L$1)) function, I get:
2.4, 2.4, 2.5, 2.5, 2.6

When I use the TREND (=TREND($A4:L4,$A$1:L$1)) Function, I get: 1.7, 2.2,
2.2, 2.3, 2.3

I would expect to get 1,2,3,1,2, for both functions.

Anyone know what I am doing wrong or if I should be using another function.

Thanks
-----------------------

From Excel's help system you can read what these functions do. If you look
there you'll see that they simply do linear extrapolation. So you give it
sawtooth data, it fits a straight line through the points and you get what
Excel's giving you.

What you say you expect to get would require some intelligence in the software,
not a simple linear fit. For example you might program the system to always
produce a high order periodic waveform and the program could determine the
constants that fit that curve to your data. Much more involved.

Bill

TREND, like LINEST, fits models that are linear in the unknown
coefficients (such as polynomials, multiple regression, etc.). Excel's
help is extremely misleading on both functions.

Beyond that, it is hard to help the OP, since he has not revealed what
values exist in most of the cells he has referred to.

Jerry

Bill Martin -- (Remove NOSPAM from address) wrote:

Mike wrote:

I am trying to €˜FORECAST or €˜TREND a series and neither function
appears to be working as I would expect. For example, I have the
following series: 1,2,3,1,2,3,1,2,3,1,2,3 and I want to FORECAST or
TREND the next 5 periods.
When I use the FORECAST (=FORECAST(M$1,$A10:L10,$A$1:L$1)) function, I
get: 2.4, 2.4, 2.5, 2.5, 2.6

When I use the TREND (=TREND($A4:L4,$A$1:L$1)) Function, I get: 1.7,
2.2, 2.2, 2.3, 2.3

I would expect to get 1,2,3,1,2, for both functions.

Anyone know what I am doing wrong or if I should be using another
function.

Thanks

-----------------------

From Excel's help system you can read what these functions do. If you
look there you'll see that they simply do linear extrapolation. So you
give it sawtooth data, it fits a straight line through the points and
you get what Excel's giving you.

What you say you expect to get would require some intelligence in the
software, not a simple linear fit. For example you might program the
system to always produce a high order periodic waveform and the program
could determine the constants that fit that curve to your data. Much
more involved.

Bill

Thanks Bill and Jerry,

Do you know of any other Functions I can use to accomplish what I a m trying
to do?
--
Mike


"Jerry W. Lewis" wrote:

TREND, like LINEST, fits models that are linear in the unknown
coefficients (such as polynomials, multiple regression, etc.). Excel's
help is extremely misleading on both functions.

Beyond that, it is hard to help the OP, since he has not revealed what
values exist in most of the cells he has referred to.

Jerry

Bill Martin -- (Remove NOSPAM from address) wrote:

Mike wrote:

I am trying to €˜FORECAST or €˜TREND a series and neither function
appears to be working as I would expect. For example, I have the
following series: 1,2,3,1,2,3,1,2,3,1,2,3 and I want to FORECAST or
TREND the next 5 periods.
When I use the FORECAST (=FORECAST(M$1,$A10:L10,$A$1:L$1)) function, I
get: 2.4, 2.4, 2.5, 2.5, 2.6

When I use the TREND (=TREND($A4:L4,$A$1:L$1)) Function, I get: 1.7,
2.2, 2.2, 2.3, 2.3

I would expect to get 1,2,3,1,2, for both functions.

Anyone know what I am doing wrong or if I should be using another
function.

Thanks

-----------------------

From Excel's help system you can read what these functions do. If you
look there you'll see that they simply do linear extrapolation. So you
give it sawtooth data, it fits a straight line through the points and
you get what Excel's giving you.

What you say you expect to get would require some intelligence in the
software, not a simple linear fit. For example you might program the
system to always produce a high order periodic waveform and the program
could determine the constants that fit that curve to your data. Much
more involved.

Bill

Jerry W. Lewis wrote...
....
Beyond that, it is hard to help the OP, since he has not revealed what
values exist in most of the cells he has referred to.
....

Dunno. Looks like the OP would be tickled pink if Excel provided
general time series forecasting, the OP's sample would be an obvious
AR(3).

What are you referring to when you say 'OP' and 'AR(3)'? I am new to the
discussion forum.

Thanks,
--
Mike


"Harlan Grove" wrote:

Jerry W. Lewis wrote...
....
Beyond that, it is hard to help the OP, since he has not revealed what
values exist in most of the cells he has referred to.
....

Dunno. Looks like the OP would be tickled pink if Excel provided
general time series forecasting, the OP's sample would be an obvious
AR(3).

David J. Braden wrote:
....

Welcome back David! Long time no see.

Jerry

You might find

http://www.netlingo.com/emailsh.cfm

a useful resource for some common and not-so-common net acronyms. Oddly
enough, OP is not there. AR(3) is a statistics acronym, not a net
acronym, as David has pointed out.

Jerry

Mike wrote:

What are you referring to when you say 'OP' and 'AR(3)'? I am new to the
discussion forum.

Thanks,

I sincerely appreciate all the attention my question has received. Like I
said, I am new to this, but I can assure you that like all of you, I will be
out here looking to help others.

I think my question has been answered. Excel does not have any functions
that allow you to forecast or trend beyond a linear trend or forecast (Which
I am very surprised). All I was trying to do was forecast a stock price out
15 days based on 45 days of history. (seems like a pretty easy function
Microsoft!).

Again, thanks, Bill, Jerry, Harlan and David,
--
Mike


"Jerry W. Lewis" wrote:

You might find

http://www.netlingo.com/emailsh.cfm

a useful resource for some common and not-so-common net acronyms. Oddly
enough, OP is not there. AR(3) is a statistics acronym, not a net
acronym, as David has pointed out.

Jerry

Mike wrote:

What are you referring to when you say 'OP' and 'AR(3)'? I am new to the
discussion forum.

Thanks,

OP: "Original Poster"
AR(3): Third-order auto regression

HTH
Dave Braden

PS- Harlan, Jerry, Mike Middleton, Conrad Carlberg, Myrna L, Tushar, Tom
Ogilvy, and several other reprobates sometimes get into discussions that
might get a little off-beat. Stephen Bullen, an absolutely brilliant fellow,
was queried by one person to the effect of "what the heck are they talking
about?!?", to which he replied "No idea; don't worry about it". There is an
astonishingly huge breadth of expertise in these newsgroups, which are
maintained at extremely high (IMHO) levels of courtesy and downright smarts.
I tend to use these forums like I would well-run classrooms: run a question
by, and you can likely get a helpful response, or series of responses. And I
look forward to what *you* can toss into the mélange of talent, be it
windmill design, aero/astro engineering, financial considerations for
Teletubbies, or something else that gets us going.

Regards,
DB

"Mike" wrote in message
...
What are you referring to when you say 'OP' and 'AR(3)'? I am new to the
discussion forum.

Thanks,
--
Mike


"Harlan Grove" wrote:

Jerry W. Lewis wrote...
....
Beyond that, it is hard to help the OP, since he has not revealed what
values exist in most of the cells he has referred to.
....

Dunno. Looks like the OP would be tickled pink if Excel provided
general time series forecasting, the OP's sample would be an obvious
AR(3).

Oh my goodess!! David B...is that REALLY you?

And yes...it's REALLY me. How are you?

Jennifer Campion
(ex-Excel MVP)



"David J. Braden" wrote:

OP: "Original Poster"
AR(3): Third-order auto regression

HTH
Dave Braden

PS- Harlan, Jerry, Mike Middleton, Conrad Carlberg, Myrna L, Tushar, Tom
Ogilvy, and several other reprobates sometimes get into discussions that
might get a little off-beat. Stephen Bullen, an absolutely brilliant fellow,
was queried by one person to the effect of "what the heck are they talking
about?!?", to which he replied "No idea; don't worry about it". There is an
astonishingly huge breadth of expertise in these newsgroups, which are
maintained at extremely high (IMHO) levels of courtesy and downright smarts.
I tend to use these forums like I would well-run classrooms: run a question
by, and you can likely get a helpful response, or series of responses. And I
look forward to what *you* can toss into the mélange of talent, be it
windmill design, aero/astro engineering, financial considerations for
Teletubbies, or something else that gets us going.

Regards,
DB

"Mike" wrote in message
...
What are you referring to when you say 'OP' and 'AR(3)'? I am new to the
discussion forum.

Thanks,
--
Mike


"Harlan Grove" wrote:

Jerry W. Lewis wrote...
....
Beyond that, it is hard to help the OP, since he has not revealed what
values exist in most of the cells he has referred to.
....

Dunno. Looks like the OP would be tickled pink if Excel provided
general time series forecasting, the OP's sample would be an obvious
AR(3).

Mike wrote:

....

I think my question has been answered. Excel does not have any functions
that allow you to forecast or trend beyond a linear trend or forecast (Which
I am very surprised).


That depends on how you are defining "linear". As noted in my first
post in this thread, TREND can do polynomial, multiple regression, etc.
forcasting. Models that are linear in the unknown coefficients are a
far richer set than just straight lines.

Also, with Solver or VBA, it is possible to fit any model that you wish,
you just have to work a bit harder. Also there are several commercial
add-ins to do more complicated analysis in Excel (try a Google search).
Or you can use Excel as a convenient interface into the widely used
statistics package R
www.r-project.org

Jerry

Hi Dave,

Long time no see. Welcome back.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
lid says...
OP: "Original Poster"
AR(3): Third-order auto regression

HTH
Dave Braden

{snip}

Mike wrote:

...All I was trying to do was forecast a stock price out
15 days based on 45 days of history. (seems like a pretty easy function
Microsoft!).

---------------------

Not as easy as you think. If you figure it out you'll be a billionaire beyond
dreams of avarice. You'll make Bill Gates look small time.

Believe me, lots of people keep at this problem and its solution is not built
into Excel or any other product you can buy. I can give you software for a
couple hundred approaches that do *not* work if you want....

Bill

Jerry---
You allude to "several commercial add-ins to do more complicated analysis in
Excel "; I assume you aren't referring to Matlab or Mathematica interfaces.
What exactly do you have in mind? Rather, what would *you* recommend, and
why?

TIA
Dave B

"Jerry W. Lewis" wrote in message
...
Mike wrote:

...

I think my question has been answered. Excel does not have any functions
that allow you to forecast or trend beyond a linear trend or forecast
(Which I am very surprised).


That depends on how you are defining "linear". As noted in my first post
in this thread, TREND can do polynomial, multiple regression, etc.
forcasting. Models that are linear in the unknown coefficients are a far
richer set than just straight lines.

Also, with Solver or VBA, it is possible to fit any model that you wish,
you just have to work a bit harder. Also there are several commercial
add-ins to do more complicated analysis in Excel (try a Google search). Or
you can use Excel as a convenient interface into the widely used
statistics package R
www.r-project.org

Jerry