Average price between dates

[fsdeniro]

I have a range of dates in column A and numbers in column B. I need to calculate the average between each quarter for every year.Therefore Q1 of 2001 would be Jan-1-01 to March-31-01.


A B
Jan-15-01 15.3
Feb-04-02 123.3
Dec-23-99 1236.3

[ This Message was edited by: fsdeniro on 2002-05-21 13:58 ]

[Aladin Akyurek]

On 2002-05-21 13:49, fsdeniro wrote:
I have a range of dates in column A and numbers in column B. I need to calculate the average between a set of dates.

A B
Jan-15-01 15.3
Feb-04-02 123.3
Dec-23-99 1236.3

Your dates do not appear to be sorted in ascending order, a state of affairs that requires a somewhat heavier formula:

=SUMPRODUCT((A2:A20>=E1)*(A2:A20<=E2),B2:B20)/MAX(1,SUMPRODUCT((A2:A20>=E1)*(A2:A20<=E2)))

or, less heavier, a longer formula with SUMIF and COUNTIF:

=(SUMIF(A2:A20,">="&E1,B2:B20)-SUMIF(A2:A20,">"&E2,B2:B20))/MAX(1,COUNTIF(A2:A20,">="&E1)-COUNTIF(A2:A20,">"&E2)





[ This Message was edited by: Aladin Akyurek on 2002-05-21 13:59 ]