Calculating average allocation using dates

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

I have a scenario where I am trying to calculate average
allocations for projects over varying time period.

PERSON: Dave PRJ A PRJ B etc
Col. b Col. c Col M Col.N
row18 4/1/2004 5/1/2004 50% 50%
row19 5/1/2004 2/1/2005 10% 40%
row20 2/1/2005 4/1/2005 10% 70%
row21 4/1/2005 9/1/2005 10% 60%

IN CELL c28 = 166 (hrs worked per month)

therefore I set up a formula for cell m22 that calculates
the average time that Dave has been allocated to PROJECT A
as follows. It is very messy and the parentheses can cause
problems if they need auditing.

=(((M18*$C$28*((YEAR(C18)-YEAR(B18))*12+MONTH(C18)-MONTH
(B18))+((M19*$C$28*((YEAR(C19)-YEAR(B19))*12+MONTH(C19)-
MONTH(B19))+((M20*$C$28*((YEAR(C20)-YEAR(B20))*12+MONTH
(C20)-MONTH(B20))+((M21*$C$28*((YEAR(C21)-YEAR(B21))
*12+MONTH(C21)-MONTH(B21)))))))))))/($C$28*((YEAR(C21)-YEAR
(B18))*12+MONTH(C21)-MONTH(B18)))

Is there a more elegant solution and formula that I should
use?
 
Something that I forgot to mention:

in some time periods there are no value or allocation and
I want the formula to exclude those 0's. e.g. if in column
N row20 there is no value the formula should not include
0% in the calculation as it reduces the average and should
not. My current formula does however include it because it
calculates the whole time period in the denominator and
not just where there is an allocation value or %.
 
Back
Top