Bar chart by month
 

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

Bar chart by month
 

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 D1:D12 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 D1:D12 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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"DCHenry" wrote in message
...
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