The formulae I posted should work ok you just need to revise the ranges used to meet your own requirements, eg:
F2:
=EDATE(EOMONTH(MIN($B$2:$B$100),-1)+1,ROWS(G$2:G2)-1)
copied down
G2:
=SUM(IF($B$2:$B$100<EDATE($F2,1),IF($C$2:$C$100>=$F2,$D$2:$D$100/(1+$C$2:$C$100-$B$2:$B$100)*IF($C$2:$C$100>=$F2,1+IF($C$2:$C$100<EOMONTH($F2,0),$C$2:$C$100,EOMONTH($F2,0))-IF($B$2:$B$100>$F2,$B$2:$B$100,$F2)))))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
copied down per Col F
Note the array entry for G2
The above would generate:
Jan-11 63.8
Feb-11 79.2
Mar-11 123
Apr-11 22
With arrays (& SUMPRODUCT) the precedent ranges should be kept as lean as possible.
100 rows or as as used here won't hurt but if using with much larger data sets it would be worth considering use of Dynamic Named Ranges (using non-volatile construct - eg INDEX & MATCH).
Bookmarks