Array and trending data.

[Angie]

Can someone explain the array function to me. And, what is the difference of
trending data using the standard trend function, and adding an array to the
trend?

Thanks,
Angie

 

[Mike Middleton]

Angie -

The "standard trend function," i.e., the TREND worksheet function, can be
directly entered as an array function by first selecting the range (multiple
adjacent cells) where the results will appear, second typing the function,
and third (instead of pressing the enter/return key) holding down shift and
ctrl while you press enter/return or holding down the apple key while you
press enter/return (called "array entering" the function).

Mac Excel Help also describes entering the function into a single cell first
and then copying to the entire range where results will appear before array
entering the function using the apple key.

For me in Excel 2004, either method gives the same results.

Relevant Help topics are "trend" and "about array formulas and array
constants."

- Mike
http://www.mikemiddleton.com

 

[Angie]

Thanks Mike,

I looked at the Excel help, and am still a bit confused as to why you would
use an array function over the standard trend function. I understand how the
formula is different, but am not exactly sure what it means.

To provide some context as to what I'm trying to do....I am trying to
project utilization rates of a particular service for the next 30 years. I
have five known utilization rates for 2000, 2001, 2002, 2003, 2004, and 2005.
My projections are annual until 2015, and then change to five year
increments after that point.

Thanks again,
Angie

 

[Mike Middleton]

Angie -

So, you could enter the years in A1:A19 (with 2000 thru 2015 in A1:A16,
followed by 2020,2025,2030 in A17:A19).

I'll assume you meant six, not five, known values for 2000-2005, entered in
B1:B6.

Select C1:C19, and type =TREND(B1:B6,A1:A6,A1:A19), but don't press
enter/return. Instead, hold down shift and ctrl while you press
enter/return.

Of course, before using TREND, you should verify visually that the known
values show an approximate linear trend and that it is reasonable to assume
that the linear trend will continue.

Also, your question is not Mac-specific, and you would likely get more and
varied responses if you posted in the
microsoft.public.excel.worksheet.functions newsgroup.

- Mike
http://www.mikemiddleton.com

 

[Jay]

Hi Mike,

I hope you don't mind me interrupting this thread. I found myself on
your site a few days ago & downloaded the LinearTrendSeasonalForecast
file as it sounded like something very apposite to my work.

Anyway, I seemed to understand it all but just wanted to check my
understanding, if that's OK.

I understand that the TREND function array-entered gives you the linear
trend data-set (as would be charted by 'add trendline (linear).

What exactly does the ratio signify? - The relationship between the
actual (seasonal) data and the trend? And am I right in thinking the
'Average ratio' is the average of this relationship for each month i.e
All Jan, Feb ratios average etc? (at least in the example given as it)

The part I'm a little hazy about is the last sheet, where the Trend is
multiplied by the Average to give the 'Forecast'. I'm a little confused
as to how this is a Forecast? Is it a forecast for a set time period
into the future? If so, how far a forecast. If you could explain it I'd
really appreciate it.

Many, many thanks,


Jason, Leeds, England.

 

[Mike Middleton]

Jay or Jason -

That workbook illustrates a method for "Classical Time Series
Decomposition," a standard method for analyzing seasonality. You may be able
to get more information using a google search. (It's one of three methods
described in Chapter 20, Time Series Seasonality, in my book, Data Analysis
Using Microsoft Excel: Updated for Office XP.)

What exactly does the ratio signify? - The relationship between the actual
(seasonal) data and the trend? <

Yes. This method uses the multiplicative model, i.e., actual values are
described with three multiplicative components: Actual = Trend * Seasonal *
Random (unexplained).

The ratio of Actual to Trend yields a result with only the (Seasonal *
Random) components.

And am I right in thinking the 'Average ratio' is the average of this
relationship for each month i.e All Jan, Feb ratios average etc? <

Yes. All Jan ratios are averaged, all Feb ratios are averaged, etc.

If a visual check of the ratios shows wide variation, it might be better to
use a trimmed mean (TRIMMEAN function) or median (MEDIAN function).

The result of averaging the (Seasonal * Random) ratios yields an average for
the Seasonal component of the model.

The part I'm a little hazy about is the last sheet, where the Trend is
multiplied by the Average to give the 'Forecast'. I'm a little confused as
to how this is a Forecast? <

Excel's TREND function projects the long-run behavior into the future (a
"forecast"), and the Average of Ratios adjusts that long-run behavior to
include the typical seasonal variation (a better forecast).

Since we are using a multiplicative model, we multiply the Trend forecast
times the Seasonal forecast to get a forecast of Actual that contains both
components.

Is it a forecast for a set time period into the future? <

Yes. Each forecast is for a specific month (Seasonal component) and year
(projection of Trend component).

If so, how far a forecast. <

That depends on your judgment about how reasonable it is to make such
projections (both the linear trend component and the average seasonal
variation component). The original poster wanted a 12-month forecast, which
seems reasonable from visual inspection of the time series data.

- Mike
http://www.mikemiddleton.com

 

[Jay]

Mike,

Many thanks for the comprehensive answer, I really appreciate it. I've
been doing some work recently on the seasonal variation in mean used car
prices & this really helps.

I appreciate you taking the time, and am going straight to amazon to
check out your book as I'm in my 6th month in a new job as an analyst &
am sure it would be invaluable.

Many thanks and all the best,

Regards

Jay

 

[Jay]

Hi Mike,

Sorry to ask more questions but do you mind my asking - in the your last
post you said:

Yes. Each forecast is for a specific month (Seasonal component) and >
year
(projection of Trend component).


That depends on your judgment about how reasonable it is to make such
projections (both the linear trend component and the average seasonal
variation component). The original poster wanted a 12-month forecast, > which
seems reasonable from visual inspection of the time series data.

I'm, just having a little trouble understanding. In the file I
downloaded the actual data was all for 2006 (per the dates on sheet1 - 2
or 3 values per month).Is that right as I'm just a little confused as I
thought it was effectively 30 month's consecutive data, hence the chart
on sheet 3. WHich is correct?

Thanks,

Jason I

 

[Mike Middleton]

Jay -

The actual data are for 30 months. There was a problem with the date format
provided by the original poster shown on my Original Data sheet, so I
converted the dates to integer months 1 to 30 on subsequent sheets. The
projections are for months 31 to 42.

- Mike

 

[Jay]

Thanks Mike,. that's what I thought but the dates on the first sheet
kept making me doubt myself

It's much clearer now - *many* thanks for your time & expertise. I
really do appreciate it and I'm sure this knowledge will come in useful.

Kind regards,

Jay