Hi,
I would like to average column B if column A is between a certain date range.
Column A Column B
Date Amount
01/01/2015 £10.00
01/01/2015 £20.00
02/01/2015 £30.00
02/01/2015 £40.00
02/01/2015 £50.00
03/01/2015 £100.00
04/01/2015 £70.00
05/01/2015 £10.00
06/01/2015 £20.00
07/01/2015 £40.00
08/01/2015 £100.00
Date Average amount
01/01/2015 - 07/01/2015 £39.00
08/01/2015 - 14/01/2015 £100.00
Thank you in advance.
Rachael
Hi there. try this:
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
How are you specifying the start date and end date of the range? It would be better to put them in separate cells, maybe in C2 and D2 with this formula in E2:
=AVERAGEIFS(B:B,A:A,">"&C2,A:A,"<"&D2)
Hope this helps.
Pete
Assuming dates are in A2:A12, amounts in B2:B12, and first date from in D3, and first date to in E3, then:
=SUMPRODUCT(($A$2:$A$12>=$D3)*($A$2:$A$12<=$E3)*$B$2:$B$12)/SUMPRODUCT(($A$2:$A$12>=$D3)*($A$2:$A$12<=$E3))
EDIT: Other solutions are more elegant!
Last edited by GaryBarlow; 03-20-2015 at 06:01 AM.
That's great.
Thank you very much
Glad you got a solution, Rachael - thanks for the rep.
If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks