Grouping dates on a chart
in [Charts]
|
From: Duncs on 29 Mar 2010 06:41 I have a workbook with 12 sheets in it, one for each month of the year. Each sheet has the day of the week in column B, several other values in columns C -- J and a transaction value in column K. What I want to do is create a chart that groups the dates into days of the week and then displays a bar showing the sum of all transactions on each of those days in the month. So, for example, the sheet for March would show: 1 x x x x x x x x 1250.00 2 x x x x x x x x 80.00 3 x x x x x x x x 3000.00 4 x x x x x x x x 5250.00 .. .. .. 30 x x x x x x x x 150.00 31 x x x x x x x x 100.00 So, based on the values above, the chart should show 7 bars with the values as follows: Monday 1250.00 Tuesday 230.00 Wednesday 3100.00 Thursday 5250.00 Friday 0.00 Saturday 0.00 Sunday 0.00 How do I achieve this? TIA Duncs
From: Luke M on 29 Mar 2010 09:29 You will need to first create a sum of all your data. On each sheet, setup a range (in the same spot, lets say AA1:AB7) List the days of the week, and in AB1:AB7, do: =SUMIF(A:A,AB1,K:K) Copied down. Use this as the data for your plot. -- Best Regards, Luke M "Duncs" <true.kilted.scot(a)gmail.com> wrote in message news:dd8d478e-a59f-4c5f-aa09-ddada3355e85(a)j21g2000yqh.googlegroups.com... >I have a workbook with 12 sheets in it, one for each month of the > year. Each sheet has the day of the week in column B, several other > values in columns C -- J and a transaction value in column K. What I > want to do is create a chart that groups the dates into days of the > week and then displays a bar showing the sum of all transactions on > each of those days in the month. So, for example, the sheet for > March > would show: > > 1 x x x x x x x x 1250.00 > 2 x x x x x x x x 80.00 > 3 x x x x x x x x 3000.00 > 4 x x x x x x x x 5250.00 > . > . > . > 30 x x x x x x x x 150.00 > 31 x x x x x x x x 100.00 > > > So, based on the values above, the chart should show 7 bars with the > values as follows: > > > Monday 1250.00 > Tuesday 230.00 > Wednesday 3100.00 > Thursday 5250.00 > Friday 0.00 > Saturday 0.00 > Sunday 0.00 > > How do I achieve this? > > TIA > > Duncs
From: Jon Peltier on 29 Mar 2010 13:36 The easiest way to apply this kind of grouping is with a pivot table. Grouping by Date in a Pivot Table http://peltiertech.com/WordPress/grouping-by-date-in-a-pivot-table/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3/29/2010 9:29 AM, Luke M wrote: > You will need to first create a sum of all your data. On each sheet, setup a > range (in the same spot, lets say AA1:AB7) > List the days of the week, and in AB1:AB7, do: > =SUMIF(A:A,AB1,K:K) > Copied down. > > Use this as the data for your plot. >
From: Duncs on 30 Mar 2010 03:48 Jon, Unfortunately, I tried a Pivot Table and it wont let me get to the level of detail that I need. I need the report to show me a sum for all Monday's, Tuesday's etc. in the month. The Pivot Table doesn't, AFAIK, let me get to that level of detail. Duncs On 29 Mar, 18:36, Jon Peltier <jo...(a)SPAMpeltiertech.com> wrote: > The easiest way to apply this kind of grouping is with a pivot table. > > Grouping by Date in a Pivot Tablehttp://peltiertech.com/WordPress/grouping-by-date-in-a-pivot-table/ > > - Jon > ------- > Jon Peltier > Peltier Technical Services, Inc.http://peltiertech.com/ > > On 3/29/2010 9:29 AM, Luke M wrote: > > > > > You will need to first create a sum of all your data. On each sheet, setup a > > range (in the same spot, lets say AA1:AB7) > > List the days of the week, and in AB1:AB7, do: > > =SUMIF(A:A,AB1,K:K) > > Copied down. > > > Use this as the data for your plot.- Hide quoted text - > > - Show quoted text -
From: Duncs on 30 Mar 2010 04:14
Luke, Cheers for that. Works great. Duncs On 29 Mar, 14:29, "Luke M" <lukemor...(a)nospam.com> wrote: > You will need to first create a sum of all your data. On each sheet, setup a > range (in the same spot, lets say AA1:AB7) > List the days of the week, and in AB1:AB7, do: > =SUMIF(A:A,AB1,K:K) > Copied down. > > Use this as the data for your plot. > > -- > Best Regards, > > Luke M"Duncs" <true.kilted.s...(a)gmail.com> wrote in message > > news:dd8d478e-a59f-4c5f-aa09-ddada3355e85(a)j21g2000yqh.googlegroups.com... > > > > >I have a workbook with 12 sheets in it, one for each month of the > > year. Each sheet has the day of the week in column B, several other > > values in columns C -- J and a transaction value in column K. What I > > want to do is create a chart that groups the dates into days of the > > week and then displays a bar showing the sum of all transactions on > > each of those days in the month. So, for example, the sheet for > > March > > would show: > > > 1 x x x x x x x x 1250.00 > > 2 x x x x x x x x 80.00 > > 3 x x x x x x x x 3000.00 > > 4 x x x x x x x x 5250.00 > > . > > . > > . > > 30 x x x x x x x x 150.00 > > 31 x x x x x x x x 100.00 > > > So, based on the values above, the chart should show 7 bars with the > > values as follows: > > > Monday 1250.00 > > Tuesday 230.00 > > Wednesday 3100.00 > > Thursday 5250.00 > > Friday 0.00 > > Saturday 0.00 > > Sunday 0.00 > > > How do I achieve this? > > > TIA > > > Duncs- Hide quoted text - > > - Show quoted text - |