 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / September 2006Array and trending data.
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 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 > Can someone explain the array function to me. And, what is the difference > of [quoted text clipped - 4 lines] > Thanks, > 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 > Angie - > [quoted text clipped - 25 lines] > > Thanks, > > Angie 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 > Thanks Mike, > [quoted text clipped - 50 lines] >> > Thanks, >> > Angie 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. > Angie - > [quoted text clipped - 73 lines] >>>> Thanks, >>>> Angie 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 > Hi Mike, > [quoted text clipped - 103 lines] >>>>> Thanks, >>>>> Angie 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 or Jason - > [quoted text clipped - 159 lines] >>>>>> Thanks, >>>>>> Angie 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 [quoted text clipped - 5 lines] > 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 > Jay or Jason - > [quoted text clipped - 159 lines] >>>>>> Thanks, >>>>>> Angie 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 > Hi Mike, > [quoted text clipped - 186 lines] >>>>>>> Thanks, >>>>>>> Angie 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 > Jay - > [quoted text clipped - 188 lines] >>>>>>>> Thanks, >>>>>>>> Angie |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.