How do I calculate a build cycle with a variable start date?

  • Thread starter Thread starter snuka
  • Start date Start date
S

snuka

I need to accomplish the following:
Amout Start 1 2 3 Jan-03 Feb-03 Mar-03 Apr-03 May-03 Jun-03 Jul-03
Aug-03 Sep-03 Total
125,000 Apr-03 15% 25%
- - 18,750 31,250
75,000 - - - 125,000
110,000 Feb-03 17% 37% 46% - 18,700
40,700
- - - -
- 110,000


I hope that came out ok... Basically I am given the amount, start date and
build cycle for a project. I have a timeline by month to populate with the
amount spread according to the build cycle starting in the 'start month.' I
need to know the formula in the month colums to do this. I would like to
accomplish this without having to add an additional table to track when the
project starts. The start date can change and the formula should be written
to accomodate this. I've been wrestling with this all weekend so I would
really appreciate some help. Thanks.
 
Hi

Into F2 (column Jan-03) enter the formula:
=IF(OR(MATCH($B2;$F$1:$N$1;0)>(COLUMN(F2)-5);(COLUMN(F2)-MATCH($B2;$F$1:$N$1
;0)-4)>3);"";IF(OFFSET($B2;;(COLUMN(F2)-MATCH($B2;$F$1:$N$1;0)-4))="";IF(OFF
SET($C2;;(COLUMN(F2)-MATCH($B2;$F$1:$N$1;0)-6))="";"";(1-SUM(OFFSET($C2;;;;(
COLUMN(F2)-MATCH($B2;$F$1:$N$1;0)-5))))*$A2);OFFSET($B2;;(COLUMN(F2)-MATCH($
B2;$F$1:$N$1;0)-4))*$A2))

and copy the formula over month's range. Maybe you have to replace
delimiters (; to ,) in formulas.
The formula works for 3 months. When there is less entries in columns 1 to
3, one additional month is calculated, so the total will be 100%. But be
aware of, that when you enter all three precents, and their sum will be
<100%, the total will be also <100%.

Arvi Laanemets
 
Back
Top