Bar chart by month

[DCHenry]

I have a spreadsheet that has month & year in column A and values (KWH) in
column B like:

Jun-00 1185
Jul-00 2636
Aug-00 3009
etc.

I would like to create a chart (and/or spreadsheet) that is grouped on the
months and shows the average value for that month and where each year is in
relation to that average. I envision a chart/speadsheet something like:

3000

2000

1000

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov
Dec

with bars on each month for that months data and a line/curve showing the
average for each month.

Thanks.
DCH

 

[Bernard Liengme]

Let's assume you data is in A1:B100
Let's assume that column A had real dates formatted to display as Jan-08
In D112 enter dates fro the first of each month (Jan thru Dec) of current
year
Format this to display as Jan, Feb using custom format mmm
In E1 enter
=SUMPRODUCT(--(MONTH($A$1:$A$4)=MONTH(D1)),$B$1:$B$4)/SUMPRODUCT(--(MONTH($A$1:$A$4)=MONTH(D1)))
Copy down to E12
Plot the table you have just made

If the A column actually has text: Jan-08:
In D112 enter the month abbreviations: Jan , Feb ,,,Dec
In E1 use
=SUMPRODUCT(--(LEFT(A$1:A$3,3)=D1),$B$1:$B$3)/SUMPRODUCT(--(LEFT($A$1:$A$3,3)=D1))
Copy down to E12
Plot chart

best wishes