Bar chart by month

D

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
 
B

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 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top