select date range then find average of values in another cell

[rob117]

Hi,

I hope someone can help me.


I have a spreadsheet that has Dates in Column A - Eg 01/01/06 to
31/12/06.
Each date has a value next to it in Column B


Can you search for between specific dates and then average the values
in column B that link to the dates in the range?


Many thanks in advance


Rob

 

[vezerid]

Hi,

I hope someone can help me.

I have a spreadsheet that has Dates in Column A - Eg 01/01/06 to
31/12/06.
Each date has a value next to it in Column B

Can you search for between specific dates and then average the values
in column B that link to the dates in the range?

Many thanks in advance

Rob

Assume you have your earliest date in D1 and your latest in D2.
=SUMPRODUCT(B2:B366*(A2:A366>=-D1)*(A2:A366<=D2))

HTH
Kostis Vezerides

 

[rob117]

Assume you have your earliest date in D1 and your latest in D2.
=SUMPRODUCT(B2:B366*(A2:A366>=-D1)*(A2:A366<=D2))

HTH
Kostis Vezerides

Thank you for your reply.

The formulae you have given doesn't average the result though by the
amount of dates selected. It totals them all together.

Thanks

Rob

 

[vezerid]

Thank you for your reply.

The formulae you have given doesn't average the result though by the
amount of dates selected. It totals them all together.

Thanks

Rob

Rob,

Thanks for the feedback. This one will get the average:

=SUMPRODUCT(B2:B366*(A2:A366>=-D1)*(A2:A366<=D2))/
SUMPRODUCT((A2:A366>=-D1)*(A2:A366<=D2))

HTH
Kostis