Thread: Calendarize?
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
MyVeryOwnSelf MyVeryOwnSelf is offline
external usenet poster
 
Posts: 213
Default 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.