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