TREND & FORECAST Functions

M

Mike

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
 
G

Guest

Mike said:
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
Click to expand...
-----------------------

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
 
J

Jerry W. Lewis

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
 
M

Mike

Thanks Bill and Jerry,

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

Harlan Grove

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.
Click to expand...
....

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).
 
M

Mike

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

Thanks,
 
M

Mike

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,
 
D

David J. Braden

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
 
J

Jennifer Campion

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

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

Jennifer Campion
(ex-Excel MVP)
 
J

Jerry W. Lewis

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).
Click to expand...


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
 
T

Tushar Mehta

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

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

HTH
Dave Braden
Click to expand...
{snip}
 
G

Guest

Mike said:
...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!).
Click to expand...

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

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
 
D

David J. Braden

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
 
Top