If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I calculate a build cycle with a variable start date?
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. |
#2
|
|||
|
|||
How do I calculate a build cycle with a variable start date?
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 "snuka" wrote in message k.net... 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. |
Thread Tools | |
Display Modes | |
|
|