A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Grouping dates on a chart



 
 
Thread Tools Display Modes
  #1  
Old March 29th, 2010, 11:41 AM posted to microsoft.public.excel.charting
Duncs
external usenet poster
 
Posts: 148
Default 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
  #2  
Old March 29th, 2010, 02:29 PM posted to microsoft.public.excel.charting
Luke M[_4_]
external usenet poster
 
Posts: 451
Default 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



  #3  
Old March 29th, 2010, 06:36 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default 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.

  #4  
Old March 30th, 2010, 08:48 AM posted to microsoft.public.excel.charting
Duncs
external usenet poster
 
Posts: 148
Default 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 -


  #5  
Old March 30th, 2010, 09:14 AM posted to microsoft.public.excel.charting
Duncs
external usenet poster
 
Posts: 148
Default 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 -


  #6  
Old March 30th, 2010, 11:52 PM posted to microsoft.public.excel.charting
Jon Peltier[_2_]
external usenet poster
 
Posts: 386
Default 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 -


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.