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
3 Answers
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.
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.
Based on your data, Excel will choose to plot the data on a daily scale (my top chart below). You can format the axis scale to use Days for its Base Unit, which will plot all data by month (my bottom chart). This gives you a marker for each data point, so multiple points for April and June.
If you want the data summarized (averaged or summed by month), use a pivot table as @mike-fitzpatrick suggests.
