Home > IT Answers > Microsoft Windows > Finding Seasonality for a large number of ite...
XENOPHON22
580 pts.
Q:
Finding Seasonality for a large number of items in Excel.
Excel 2007, Date calculation, Excel Calendars
I am trying to establish any seasonality with what is, unfortunately, a large number of items and a relatively small time sample.  I have 32,000 different items which have typically sold at a steady pace over the past three years.  I have the sales of each item by month for the previous three years and am trying to identify those with larger sales over particular recurring monthly periods.

I am currently using a few ideas which pit single or small groups of consecutive months against the next year.  Does any one have a better idea?


Thank you.


Software/Hardware used:
EXCEL 2007
ASKED: Nov 11 2009  7:36 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
Labnuke99
27615 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
Try the frequency function in Excel to parse out ranges of the values for each month. You could have sales of a certain dollar value or sales of specific quantities in the bin ranges. See the links below for more information on the frequency function.

http://support.microsoft.com/kb/100122
http://www.meadinkent.co.uk/xlfreq.htm
Google video search for Excel Frequency function videos
Last Answered: Nov 11 2009  8:13 PM GMT by Labnuke99   27615 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

XENOPHON22   580 pts.  |   Nov 23 2009  2:58PM GMT

Last week, between taking care of my primary duties, I tried using frequency functions on this data but could not find a discernible method by which to sort these out. This would be helpful in a handful of items but I have over 30k to review.

Any other ideas?

 

Labnuke99   27615 pts.  |   Nov 23 2009  9:58PM GMT

I use this function on about 2k items at a time. You could still use it on a large number of items, you just need to break down the categories into reasonable chunks or percentages. You could filter and then run the frequency function and that might help. The videos gave some good ideas about that process.