Calendarize?
Hi. I am attempting to calendarize quantities across 12 months using
some type of automated function (as the start dates and quantities
will change per project and I don't want this to be a manual
function). I've gotten as far as "telling" the application that if a
project duration begins in a particular month, to put quantities in
the start month...but the kicker (problem) is that I don't know how to
make the application spread the quantities over the other months
(duration) of the project...for instance, a project begins in April
and ends in June. I need to see 200 pages per month (project totals
600 pages with equal amounts of pages being completed over the 3-month
period); the program reads that I need 200 pages in April, but I don't
know how to get the rest of the pages for May and June to read--and
then end at June...can anyone help?
The following suggests one way to proceed.
In column A put
Start
Months
Pages
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
In B1 put
Nov
In B2 put
4
In B3 put
600
In B4 put
=IF(B$1=$A4,B$3/B$2,"")
In B5 put
=IF(B$1=$A5,B$3/B$2,IF(B4="","",
IF(COUNT(B$4:B4)<B$2,B$3/B$2,"")))
and copy B5 down to B15.
This example shows starting in Nov for 4 months with 600 pages total. For
other examples, change B1, B2, and B3.
|