How would I return a monthly average with below cells without manually selecting each month separately?

  A           B
1 2013-02-13  25600
2 2013-02-14   5000
3 2013-03-15  12300
up vote 4 down vote accepted

Pivot tables are the obvious answer. Give your columns titles (Date, Value) in the top row, then create a pivot table.

Add Date as your row labels, then right-click any date and group by month.

Then add your Value column to the values section of your pivot table. Right-click any value and choose summarise values by > average.

Job done!

  • 1
    The trick of "right-click any date and group by month" is priceless. What I learnt also is you can group by both YEAR and MONTH by holding CTRL key :) – Korayem May 3 '14 at 20:20

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.