Using Lookup to distribute amount over a given duration

**StarHunter** · 10-16-2012, 05:39 AM

Hello,
I'm trying to do what Primavera simply does but with Excel.
I have few activities with their durations start and finish times and their total amount and I want to determine the cost for every week.
Now I need a function to automatically place the Amount/Duration for each activity in its place.
For Example, Activity A with a 5 weeks duration starts at week 1 and finishes at week 5 with an amount of 50,000$
It means that week 1 will have 10,000$ and so on till week 5 and the same with all other activities.
I want the formula to re-distribute the amount over the duration if I changed the start or finish times.
Is that possible?
Thanks

**Richard Buttrey** · 10-16-2012, 05:59 AM

Hi,

Your data isn't consistent

D3 says the finish week is week 5 whereas the other finish weeks are the sum of the duration plus the start week. Furthermore your actual analysis is not consistent. Task B is said to start at week 2, but the analysis starts at week 3.

However see if the attached does what you want.

**StarHunter** · 10-16-2012, 06:20 AM

Thanks Richard.
I'll try to explain. Week 1 is actually 0 time because when I say that an activity will start at a certain time it does start when that time has finished in order to start. I mean it's a finish to start relationship. In other words, activity B starts at week 2.. it's the finish of week 2 and the start of week 3.
Activity A should actually start at 0 and ends by 5 but I changed the 0 to be week 1 because I thought I'd use lookup formula..but you made it easirer for me

Again thanks for everything