Excel - Bar chart by month

Asked By DCHenr on 20-Mar-09 12:37 PM
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 replied on 20-Mar-09 02:03 PM
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