joeu2004 schrieb am 04.03.2015 12:30:47 mit Betreff " How to
Calculate the average Daily price within a time period":
Errata....
"joeu2004" wrote:
=IF(G1<$A$1,NA(),SUMPRODUCT((G1<=A2:A10)*(A1:A9<=G 2+1),B1:B9,
(G2+1<A2:A10)*(G2+1)+(G2+1=A2:A10)*A2:A10-(G1=A1:A9)*G1-(G1<A1:A9)*A1:A9)

/ (G2+1-G1))

I forgot to add the appropriate absolute references, to wit:

=IF(G1<$A$1,NA(),SUMPRODUCT((G1<=$A$2:$A$10)*($A$1 :$A$9<=G2+1),$B$1:$B$9,
(G2+1<$A$2:$A$10)*(G2+1)+(G2+1=$A$2:$A$10)*$A$2:$ A$10
-(G1=$A$1:$A$9)*G1-(G1<$A$1:$A$9)*$A$1:$A$9)
/ (G2+1-G1))

Great.
Thanks for sharing.
Alfred