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
0
Duncs
3/29/2010 10:41:17 AM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
859 Views

Similar Articles

[PageSpeed] 26

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@gmail.com> wrote in message 
news:dd8d478e-a59f-4c5f-aa09-ddada3355e85@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 


0
Luke
3/29/2010 1:29:32 PM
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.
>
0
Jon
3/29/2010 5:36:47 PM
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...@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 -

0
Duncs
3/30/2010 7:48:30 AM
Luke,

Cheers for that.  Works great.

Duncs

On 29 Mar, 14:29, "Luke M" <lukemor...@nospam.com> wrote:
> You will need to first create a sum of all your data. On each sheet, setu=
p a
> range (in the same spot, lets say AA1:AB7)
> List the days of the week, and in AB1:AB7, do:
> =3DSUMIF(A:A,AB1,K:K)
> Copied down.
>
> Use this as the data for your plot.
>
> --
> Best Regards,
>
> Luke M"Duncs" <true.kilted.s...@gmail.com> wrote in message
>
> news:dd8d478e-a59f-4c5f-aa09-ddada3355e85@j21g2000yqh.googlegroups.com...
>
>
>
> >I have a workbook with 12 sheets in it, one for each month of the
> > year. =A0Each sheet has the day of the week in column B, several other
> > values in columns C -- J and a transaction value in column K. =A0What 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. =A0So, for example, the sheet for
> > March
> > would show:
>
> > 1 =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 1250.00
> > 2 =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 =A0 =A0 80.00
> > 3 =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 3000.00
> > 4 =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 5250.00
> > .
> > .
> > .
> > 30 =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 =A0 150.00
> > 31 =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 x =A0 =A0 100.00
>
> > So, based on the values above, the chart should show 7 bars with the
> > values as follows:
>
> > Monday =A0 =A0 =A0 =A0 1250.00
> > Tuesday =A0 =A0 =A0 =A0 =A0230.00
> > Wednesday =A0 3100.00
> > Thursday =A0 =A0 =A0 5250.00
> > Friday =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 0.00
> > Saturday =A0 =A0 =A0 =A0 =A0 =A0 0.00
> > Sunday =A0 =A0 =A0 =A0 =A0 =A0 =A0 0.00
>
> > How do I achieve this?
>
> > TIA
>
> > Duncs- Hide quoted text -
>
> - Show quoted text -

0
Duncs
3/30/2010 8:14:19 AM
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 Peltier<jo...@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 -
>
0
Jon
3/30/2010 10:52:27 PM
Reply:

Similar Artilces:

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

Problem with xy scatter with dates
I am trying to create an x-y scatter chart where both the x values and the y values are dates. I highlight the 2 adjacent columns of date data and click on Insert>Scatter. Instead of a scatter graph, I get a line chart with 2 lines (the two data columns). The x values on the graph are simply a count of the data points as in a line chart. If the 2 columns of data are not dates, I get the correct scatter chart, but not if the x-values are dates in which case I get a line chart. How can I get a scatter chart with both x-values and y-values as dates? You could select just the Y val...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

Make Table Query
I have a Macro and at the end of the Macro I have a Make Table Query that I would like to automatically save as todays date in the current database. Does anybody have an idea on how I can do this? On Wed, 06 Jun 2007 11:40:24 -0700, dswiders@gmail.com wrote: >I have a Macro and at the end of the Macro I have a Make Table Query >that I would like to automatically save as todays date in the current >database. Does anybody have an idea on how I can do this? A date is just a field value. Saving today's date as a Table is almost certainly VERY bad design. Why do an expensive, awk...

Add Actual End Date to Resolved Cases view
Hi We would like to add the actual end date to the Case General Tab or possibly to the Resolved Cases and/or My Resolved Cases view. I know that the Actual End Date is available on the Service Activity and Case Resolution but this field is not available for the Case. Any workaround for this? Thanks Mark ...

Problem bringing up Group Policy on Domain Controller
I loaded the Group Policy snap in on my xp machine and got the following error on my Server 2000 domain controller: "The following entry in the [strings] section is too long and has been truncated" error message when you try to modify or to view GPOs in Windows server 2003, Windows XP Professional or Windows 2000. This is a well-documented and there is a hot fix. After I applied the hot fix the error was gone, but I still cannot access some group policies. Why and what can I do. My boss likes to work ar the server console and he cannot do that and is furious. You need to...

Can we use group policy templates for Outlook client options
Hi there, We are looking to automate the CRM outlook client settings so we don't have to manually change them for each user. Is there any way we can do this through group policy or another way? It would be handy if we had a list of registry settings that we could use? Thanks ...

How do I delete chart superimposed on Excel document?
Created Excel document. Print preview did not show gridlines. In attempting to get gridlines, somehow got into charts. A chart was created and is superimposed on original document. How do I get rid of the chart? How do I get gridlines on to original document? Oz Right-click on the chart and Edit>Clear>All To print gridlines.................File>Page Setup>Sheet Check "gridlines" and uncheck "draft quality". Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 14:27:01 -0700, Oz Osborn <Oz Osborn@discussions.microsoft.com> wrote: >Created Excel docume...

Date Picker
Hello, Is there a DatePicker in Excel 2007 so I can pick a date when I focus on a cell? If not, where can I get one? Thanks, Miguel Look here: http://www.rondebruin.nl/calendar.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "shapper" <mdmoura@gmail.com> wrote in message news:776296c8-83d8-467e-aefb-c31b24446bf7@b15g2000yqd.googlegroups.com... > Hello, > > Is there a DatePicker in Excel 2007 so I can pick a date when I focus > on a cell? > > If not, where can I get one? > > Thanks, > Miguel ...

Dates are not displayed in ComboBox
I have a From ComboBox and a To ComboBox on a Form; cboFrom and cboTo. The Row Source is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; The SQL is this: SELECT DISTINCT SharePrices.DateTime FROM SharePrices ORDER BY SharePrices.DateTime; Nothing is showing in either ComboBox; all I see is a whole bunch of blanks. I just did this in another form in another Access database. I can’t figure out why it’s not working for me now. I’m using Access 2007. I’d sincerely appreciate any insight with this. Thanks! Ryan -- Ryan--...

Chart: range info from seriescollection?
I would like to fill a combobox (Rowsource) with data that are used in for instance the XValues or Values of a Seriescollection of a graph. "ActiveChart.SeriesCollection(1).Values" does not give the right type (I was hoping for a string type of info like "Sheet1.Range(a1:a10)"). How to get a Range to be used as Rowsource for my combobox??? Thanks for your suggestions, JB The Values and XValues will return an array of values. To get the range address like this, you will need to parse the series formula. Or you could use John Walkenbach's class module for extractin...

Is it possible to save an excel chart as .emf?
I would like to save an excel chart as .emf instead of .jpeg or .gif using the script below. Is this possible? Sub SaveChartAsGIF () Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif" ActiveChart.Export FileName:=Fname, FilterName:="GIF" End Sub ...

Step change charts
Help, I am trying to create a bar charts by year but only want to show increases and decreases through the middle years as step changes i.e. 1999 100 2000 +10 2001 -30 2002 +40 2003 120 How can I do this? What I should see is the first vertical bar being 100 then from the 100 level an increase of 10 to get to 110 then a decrease bar of -30 (from the 110 level) to get to 80 etc etc. For a tutorial, see the Excel | Charts | Waterfall Chart page of my web site. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Cus...

Excel Bar Chart with many elements Example
I am working with a survey database where one field represents one question in the Survey. Some questions allow the user to enter multiple answers. This data isn't normalized but I have t work with what I've got. Anyway I tried to create a chart in Acces with the wizard but got the error: "Cannot have more than elements in the chart". Anyway I attempted to recreate the char in Excel but my chart looks crammed and canno print entirely within one page. Does anyone know how or have a example of how to create a bar chart with many elements in a clea and consice manner T...

Can no longer cut/paste ranges in chart "edit data" fields?
In Excel 2007 I can no longer cut/paste cell ranges into the x and y datasets using the edit data tool. Is there a way to turn this back on? Also the chart range resets itself to the current length of the data, however, in many instances I want to set an exact future range. Is there a way to have excel not reset the range to what it "thinks" I want. > In Excel 2007 I can no longer cut/paste cell ranges into the x and y > datasets > using the edit data tool. Is there a way to turn this back on? I have no problem doing what you can't. Could you provide more det...

Year to date sales history of inventory
How can this system provide me with cumulative sales numbers of a particular item ie: How many total gym shorts did we sell in 12 months? Detailed Sales Report with a custom date range. In the body of the report look at the top line (Overall) that will give you the answer you seek my child. Now go caterpillar blossom into a butterfly. Rob "KatieH" wrote: > How can this system provide me with cumulative sales numbers of a particular > item ie: How many total gym shorts did we sell in 12 months? Musta got a new batch o glue, huh? (g) KatieH To pick up where Rob started...

Excel 2003 - VBA
Does anyone have and idea how to create the date of the last day of the previous month? Craig Hi Craig =Dateserial(year(date), month(date), 0) HTH. Best wishes Harald "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nbdc.sbc.com... > Does anyone have and idea how to create the date of the last day of the > previous month? > > Craig > > =TODAY()-DAY(TODAY()) -- __________________________________ HTH Bob "Craig Brandt" <brandtcraig@att.net> wrote in message news:yvVxk.22131$89.8111@nlpi069.nb...

Conditional formatting with dates formula problem.
Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

email groups
I have tried to send group emails of approx 40 names from my outlook address book......I got bounce backs from 12 the first time I sent and 12 additional names the second time...they are from different domains Technical details of permanent failure: PERM_FAILURE: Probe failed: Illegal To: address (invalid domain name): 'rita311@optonline.net' this is the message I get....can anyone help ? Regards Angelo39 ...

can not see chart
I created a simple chart. For some odd reason the chart is all gray. I can only see the chart if i select the chart object. Please help! Thank you! Hi, Via the menus Tools > Options. On the View tab select Obejcts Show All. Cheers Andy Nick wrote: > I created a simple chart. For some odd reason the chart is all gray. I can > only see the chart if i select the chart object. Please help! Thank you! -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Access autonumber field changes to date format when Excel imports
When importing Access data into Excel, the autonumber field in Access is chenged to date format instead of a general number format. I could run a macro to change the number format but this only happens on some Vista machines, not all. Any suggestions as to why this is happening? ...

Group
I want to group my business e-mail addresses, keeping them seperate from my everyday contacts. I can't find in help where I can do this grouping. Sorry to bother you on such a trivial question. -- Thank-you Carl R Check 'Categories' in help "Carl R" <carlritcey@bendbroaband.com> wrote in message news:7E27F9E8-81B4-42B6-B30C-9970FA0F796C@microsoft.com... > I want to group my business e-mail addresses, keeping them seperate from my > everyday contacts. I can't find in help where I can do this grouping. Sorry > to bother you on such a trivial questio...

What date was a batch created
I'm in GP v8.0. I need to know what date a user created and/or saved a batch. I've looked all over and there doesn't seem to be any trail when a batch was created/saved short of turning on the User Tracking system. Please get back to me ASAP - sdanna@sfgmortgage.com First, as you probably know, this is not a forum for getting instant answers because it is not monitored officially. Second, SY00500 stores the batch history. It is in the company database. You will need to create a report based on this table. -- Charles Allen, MVP "Sandy D''Anna, GP Project Ma...

How to avoid when linked source data is changed, chart turns blan.
When I created new charts in Excel 2003, each previous chart became blank. Therefor I saved the file unter a new name and copied the previous chart to the new file. This created ofcourse links to the other files. Now I want to change the source file, but when I do that the chart becomes blank as well. Anybody who had this problem before and found out how to solve it ? Rgds, Chris ...

conditional format a date in Excel
I am trying to use conditional formatting to show when a date is less than 30 days in the future. When I use "cell value" "is less than" "="Now()+30" all the dates change. I am trying to use conditional formatting to show when a date is les than 30 days in the future. When I use "cell value" "is less than" "="Now()+30 all the dates change. lose the quotes in the condition =Now()+30 Unless you mean between today and 30 days in the future in which cas =now() would be the first condition and =now()+30 would be the second Al...