I need to calculate the average Daily price of a particular product within a specified time period i.e. between two dates, Sep 16 2014 & Feb 15 2015

The daily price of the product, during the above-mentioned time period, has revised as per the table below

Aug 31 2014 : $74.58
Oct 01 2014 : $75.46
Oct 15 2014 : $74.29
NOv 01 2014 : $71.68
Dec 01 2014 : $70.73
Dec 16 2014 : $68.65
Jan 12 2015 : $68.66
Jan 17 2015 : $66.64
Feb 04 2015 : $64.60


Thanks for the help

San

Hi,

Am Mon, 2 Mar 2015 04:19:23 -0800 (PST) schrieb San:

Aug 31 2014 : $74.58
Oct 01 2014 : $75.46
Oct 15 2014 : $74.29
NOv 01 2014 : $71.68
Dec 01 2014 : $70.73
Dec 16 2014 : $68.65
Jan 12 2015 : $68.66
Jan 17 2015 : $66.64
Feb 04 2015 : $64.60

try:

=AVERAGEIFS(B:B,A:A,"=" &DATE( 2014,9,16),A:A,"<="&DATE(2015,2,15))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Monday, March 2, 2015 at 5:49:30 PM UTC+5:30, San wrote:
I need to calculate the average Daily price of a particular product within a specified time period i.e. between two dates, Sep 16 2014 & Feb 15 2015

The daily price of the product, during the above-mentioned time period, has revised as per the table below

Aug 31 2014 : $74.58
Oct 01 2014 : $75.46
Oct 15 2014 : $74.29
NOv 01 2014 : $71.68
Dec 01 2014 : $70.73
Dec 16 2014 : $68.65
Jan 12 2015 : $68.66
Jan 17 2015 : $66.64
Feb 04 2015 : $64.60


Thanks for the help

San

Thanks Claus, but isn't that a simple average.. Haven't checked it yet. but I need a weighted average depending upon the no. of days that the price remained constant.

San

San schrieb am 03.03.2015 04:26:44 mit Betreff " How to Calculate the
average Daily price within a time period":
On Monday, March 2, 2015 at 5:49:30 PM UTC+5:30, San wrote:
I need to calculate the average Daily price of a particular product within a specified time period i.e. between two dates, Sep 16 2014& Feb 15 2015

The daily price of the product, during the above-mentioned time period, has revised as per the table below

Aug 31 2014 : $74.58
Oct 01 2014 : $75.46
Oct 15 2014 : $74.29
NOv 01 2014 : $71.68
Dec 01 2014 : $70.73
Dec 16 2014 : $68.65
Jan 12 2015 : $68.66
Jan 17 2015 : $66.64
Feb 04 2015 : $64.60
Dec 31 2999 : 0.00 <-------------


Thanks for the help

San

Thanks Claus, but isn't that a simple average.. Haven't checked it yet. but I need a weighted average depending upon the no. of days that the price remained constant.

San

In this case you need a helper column, say C.
It shall contain (days within the interval) * price.
Say, you put your start date (Sep 16 2014) in G1, your end date (Feb 15
2015) in G2.

Furthermore you need a high date, say Dec 31 2999, at the end of your
table! Otherwise the formula will get more complicated. *)
Column B (the price) in this last row does not matter.



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)



Note that the day of your end date is not taken into account. If you
want Feb 15 to be counted, write Feb 16 into G2.
(Or replace everywhere in both formulas "$G$2" by "$G$2+1".)

*)
That is, you have to replace both occurrences of "A2" in the formula by
"IF(ISBLANK(A2),DATE(2999,12,31),A2)", if you want to get along without
an ultimate high date at the end of your table.

Best regards,
Alfred

"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