"Alfred Heiligenbrunner" wrote:
In C1 put this formula:
=IF(OR(A2<$G$1,A1$G$2),0,MIN(A2,$G$2)-MAX(A1,$G$1))*B1
and fill it down. Your average is
=SUM(C:C)/($G$2-$G$1)

Thanks for that! I have been struggling for 3 days to find a relatively
simple formula. Your design provided the structure that had eluded me.

We can do this without a helper column. Alternatively, array-enter the
following formula (press ctrl+shift+Enter instead of just Enter):

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

That assumes the table of dates and prices is in A1:B10, with 31 Dec 9999
(max possible date) in A10. B10 can be empty.

And it assumes the start date is in G1, the end date is in G2, and we want
to calculate the daily average over that period inclusive of those dates
(i.e. G2+1-G1).

The formula works correctly only if G1 is not before the first date in A1.
Otherwise, the formula returns bogus results. Of course, we could add a
sanity check by array-entering the following formula (press ctrl+shift+Enter
instead of just Enter):

=IF(G1<$A$1,NA(),SUM(...)/(G2+1G1))

-----
Not recommended.... If you want to avoid an array-enter formula, you can
normally-enter the following formula (press Enter as usual):

=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))

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))

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