MATH HELP!

[ADAM]

I have sales data that is reported in 13 periods for a
year (13 periods x 28 days each = 364 days) I'm trying to
create a forumala(s) that shoehorns this into a monthly
calendar in such a way as to maintain the variability
found in the periods, as well as the variability created
by differing days in the month.

I know it is a long shot, but any help would be greatly
appreciated!

 

[Frank Kabel]

Hi
without knowing the exact design of your spreadsheet I would try the
following
- add a helper column which calculates the month for each day. E.g. if
you have stored the date in column a enter
=MONTH(A1)
or
=TEXT(A1,"mmmm")
in a helper column

To create a monthly report you may have a look at pivot tables. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
for more details

 

[ADAM]

Thanks for the response. Unfortunately, I don't have any
daily information. I realize that some assumptions will
have to be made...

 

[Norman Harker]

Hi Adam!

All you'll get is approximations unless you have daily information
that you can use to translate what you have into months.

For example (Last year! nb this year will be different because Feb had
29 days)

January will be
Lunar1+3/28*Lunar2
February (last year)
25/28*Lunar2+3/28*Lunar3
March
25/28*Lunar3+6/28*Lunar4
April
22/28*Lunar4+8/28*Lunar5
etc.

You'll end up with a missing day (for 2003) or two days for 2004.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[ADAM]

Hi Norman,

Thanks for responding. Yes, I am looking for
approximations. Unfortunately, I am a bit of a novice and
your Lunar reference is beyond me. If it is not too much
trouble, could you simplify it for a simpleton?

Thanks!

 

[Norman Harker]

Hi Adam!

You have 13 four week periods. I have named them Lunar1, Lunar2 etc.

All I'm saying is that:
All of the first 28 day period fall in January plus 3 of the second 28
day period
The remaining 25 days of the second 28 day period are in February plus
(in 2002) 3 of the third 28 day period.


Since you haven't got daily data I've apportioned the 28 day totals


We need information on layout before we can assist further.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.