|
Grouping dates on a chart
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 |
Grouping dates on a chart
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" wrote in message ... 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 |
Grouping dates on a chart
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/gro...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. |
Grouping dates on a chart
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 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 - |
Grouping dates on a chart
Luke,
Cheers for that. Works great. Duncs On 29 Mar, 14:29, "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. -- Best Regards, Luke M"Duncs" wrote in message ... 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 - |
Grouping dates on a chart
I think I've done that with a dummy column that contains the name of the
days of the week. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3/30/2010 3:48 AM, Duncs wrote: 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 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 - |
| All times are GMT +1. The time now is 02:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com