If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 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 -- 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 |
Thread Tools | |
Display Modes | |
|
|