Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've the following data in an excel spreadsheet:

Date (dd/mm/yyyy)     Value
----------------- ---------
   01/04/2011        123.45
   03/04/2011       7234.25
   16/05/2011         80.00
   11/06/2011        223.36
   12/06/2011        455.97
   29/06/2011       2345.12

I want to insert a chart with Month in the X axis and Value in the Y axis (that is, value sumarized by month).

Can I do that with a scatter chart (or using any other excel feature) without adding auxiliary calc in other cells?

If I can't, how can get the chart described with auxiliary calcs?

Note: I'm using excel 2010

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Use a Pivot Chart and add month/year grouping on the Date field and your summarization choice (sum, average, count, etc.) on the Value field.

share|improve this answer
    
Yes, the built in tools make sense. –  jonsca Jul 9 '11 at 3:17
   A        B    C   D
1/12/2011   1   200 200
2/13/2011   2   34  34
3/24/2011   3   1   79
3/26/2011   3   78  22.5
4/10/2011   4   10  0
4/12/2011   4   12.5    

Where the formula in D is =IF(YEAR(A:A)=2011,SUMIF(B:B,"="&ROW(),C:C),0) and where column B is defined by Month(a:a)

It checks to make sure the dates are in the current year (skip if you don't need this), and then sums the values from months 1,2,3,4... into rows 1,2,3,4.

Then, make your chart out of column D like you normally would.

share|improve this answer
    
I inadvertently Americanized your dates, but same idea. –  jonsca Jul 8 '11 at 21:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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