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

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!

| improve this answer | |
  • 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 agree to our terms of service, privacy policy and cookie policy

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