having trouble creating chart to show monthly totals

Hi

I'm using office XP.

I keep a single spreadsheet were I record orders received.

Its a very simple sheet with one order per row.

In col B I place the date the order was received (in the format DD/MM/YY)

I'd like to make a chart (pie or column) which displays the total number of 
orders per month so that I can see at a glance when were busy and quieter 
periods.

I've tried using the wizard but all I ever get is a mess and not what I 
expect to see at all.

I suspect I may need to separate my data by month?  But is there a way for 
Excel to work that out for itself from a single column of dates?

Maybe I'm expecting far too much or I haven't laid out my data correctly.

I'm assuming Excel can distinguish the dates by month and then calculate the 
total number of orders received within each month.

Your guidance would be much appreciated.

Many thanks for your time.

Ian 


0
nospam7515 (2085)
6/4/2005 12:39:43 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
585 Views

Similar Articles

[PageSpeed] 58

  this might be of help but probably easier to set up months & amounts using
sumproduct
  http://peltiertech.com/Excel/Pivots/pivotstart.htm


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Ian Roberts" <nospam@NOSPAM.com> wrote in message
news:%23Xr1pJQaFHA.3528@TK2MSFTNGP09.phx.gbl...
> Hi
>
> I'm using office XP.
>
> I keep a single spreadsheet were I record orders received.
>
> Its a very simple sheet with one order per row.
>
> In col B I place the date the order was received (in the format DD/MM/YY)
>
> I'd like to make a chart (pie or column) which displays the total number
of
> orders per month so that I can see at a glance when were busy and quieter
> periods.
>
> I've tried using the wizard but all I ever get is a mess and not what I
> expect to see at all.
>
> I suspect I may need to separate my data by month?  But is there a way for
> Excel to work that out for itself from a single column of dates?
>
> Maybe I'm expecting far too much or I haven't laid out my data correctly.
>
> I'm assuming Excel can distinguish the dates by month and then calculate
the
> total number of orders received within each month.
>
> Your guidance would be much appreciated.
>
> Many thanks for your time.
>
> Ian
>
>


0
Don
6/4/2005 1:15:44 PM
You are organizing your data in the correct manner.  What you want can 
be done as follows.  Add a new column to your data table.  Say column C 
is available.  Then in C1 enter the literal 'OrderMonth'.  In C2 enter 
=MONTH(A2).  Copy C2 as far down column C as needed.

Now, create a PivotTable (Data | PivotTable and PivotChart Report...) 
-- keep in mind MS has changed the name of this menu item over time.  
For the row field use the new field we created.  For the data field use 
either field.  By default XL will create a 'Sum of xxxx' field.  Double 
click that label and in the resulting dialog box change the Sum to 
Count.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <#Xr1pJQaFHA.3528@TK2MSFTNGP09.phx.gbl>, nospam@NOSPAM.com 
says...
> Hi
> 
> I'm using office XP.
> 
> I keep a single spreadsheet were I record orders received.
> 
> Its a very simple sheet with one order per row.
> 
> In col B I place the date the order was received (in the format DD/MM/YY)
> 
> I'd like to make a chart (pie or column) which displays the total number of 
> orders per month so that I can see at a glance when were busy and quieter 
> periods.
> 
> I've tried using the wizard but all I ever get is a mess and not what I 
> expect to see at all.
> 
> I suspect I may need to separate my data by month?  But is there a way for 
> Excel to work that out for itself from a single column of dates?
> 
> Maybe I'm expecting far too much or I haven't laid out my data correctly.
> 
> I'm assuming Excel can distinguish the dates by month and then calculate the 
> total number of orders received within each month.
> 
> Your guidance would be much appreciated.
> 
> Many thanks for your time.
> 
> Ian 
> 
> 
> 
0
6/5/2005 5:36:41 PM
Reply:

Similar Artilces:

Creating a Group Mailbox
Is ther a way to create a group mailbox in Exch 2003. In 5.5 you could create a new mailbox, and add users to it. I can't find a way to create a mailbox in 2003 without first creating a user. WTF? Sanonymous@discussions.microsoft.com wrote: > Is ther a way to create a group mailbox in Exch 2003. In > 5.5 you could create a new mailbox, and add users to it. > I can't find a way to create a mailbox in 2003 without > first creating a user. WTF? See http://www.msexchange.org/tutorials/MF006.html Use a Public Folder: http://www.msexchange.org/articles/MF021.html --...

Creating a chart from a table of values
I would like to create a chart from a table of values using indexing so i can vary the range of the table. Can anyone help? Take a look at the dynamic charts here http://www.peltiertech.com/Excel/Charts/Dynamics.html#DynoCht "hydro1guy" wrote: > I would like to create a chart from a table of values using indexing so i can > vary the range of the table. Can anyone help? ...

Error 429
I am getting the error "Error 429: activeX component can't create object". Here is all the code (it executes when I click on a control in a form): Private Sub Command0_Click() Dim sock1 As Winsock Set sock1 = New Winsock sock1.RemotePort = 1000 End Sub Any insight would be appreciated ...

Email trouble with New User in Active Directory
Below is a trail of posts that have been made. My problem has yet to resolved and I need some help quickly. The only other thing I can think of is to reinstall Exchange Server 2003. Hopefully someone will know the answer. Thanks! Subject: Re: Email not Setup when new user created in Active Directory From: "Bryan Hill" <bthill@comcast.net> Sent: 8/15/2004 12:30:21 PM We have other clients that are working fine. Just when we try to set up new ones - it will create the SMTP address but not the X400 address. I tried manually forcing the X400 but it will...

Need to change the display of a chart
Hello! I have several pie charts that I have created to show percentages. In several of them, there are multiple legend values for the same percentage (0%). Excel prints all of them stacked on top of each other, with their leader lines and in some cases it runs into the title of the chart. Is there any way to change this? Also, in some other cases, with the same situation, there are no leader lines displayed (even though I told it to under Chart Options) and the values all print on top of each other. Suffice it to say, these charts are not very readable. Can anyone help me on thi...

Default view is set to show Reading Pane
This is set on all folders. At the moment I have been going to each individual folder and turning the reading pane off. Is there a way to turn this off as a default on all folders? Thank you. Kylie B Kylie B <KylieB@discussions.microsoft.com> wrote: > This is set on all folders. At the moment I have been going to each > individual folder and turning the reading pane off. > > Is there a way to turn this off as a default on all folders? Use the techniques described here: http://www.outlook-tips.net/howto/grouping.htm -- Brian Tillman ...

How do I create Page 1 of X style page numbers?
I wish to create page numbers that automatically update themselves in this format: Page 1 of X (i.e. a 10 page document would say Page 1 of 10, Page 2 of 10, etc). Can this be done in Publisher automatically? Go to the master page, type *page* before the # and *of 10* after the #. (Without the asterisk) -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Paresh" <Paresh@discussions.microsoft.com> wrote in message news:50BEFAA8-6EFD-48DC-ABAC-F117296BF0B1@microsoft.com... >I wish to create page numbers that automa...

Surface Chart Legend
I have a surface chart with several bands of various color defs. Occassionally, but not always, when the source data is modified ALL bands take on a purple color. I then have to click on each line of the legend and reformat it for the intended color. I have not discovered what causes this or how to stop it from occurring. Thank You for any tips on how to lock this key in. Bryan ...

Excel charts -retaining subscript in labels from worksheet data
Excel 2007 - Chemical formulae entered in cells using format to show subscript, however normal font shows on formula bar. When copied and pasted elsewhere, or when used in chart subscript characters show as normal font. Help! Hi Polly, Your problem is quite analogous to the repeatedly discussed Chemical Legend. Luckily enough, there are Chemical fonts that include numeral subscripts and superscripts as individual characters. If you apply these specific fonts in the place of your (most frequently used) basic fonts: Arial or Times New Roman, you are able to write down chemical formula...

Form Wizard Unable to create form
After navigating through create form wizard I get message saying wizard unable to create form. Can anyone help me with this . I am using Vista Home Premium and Office 2003 Profesional. What EXACTLY does the error message say? How many fields are in the form's Record Source? The usual message is that Access can't create a form because there are too many fields, which means just that. The forms wizard is intended to help the casual user create a simple form. Complicated form with bunches and bunches of fields doesn't fall under this heading. These forms have to be created using ...

When are .mbf files created?
I am trying to figure out a problem and I noticed that Money seems to create backup files (.mbf). Does anyone know what causes Money 2003 to do this? I noticed three of these files created within a 10 minute period one day but no other ones that I see. Does it also delete older ones according to some rotating scheme? I also noticed something called "my portfolio Backup.mbf" that was created last nite. I know that I did not do this myself. Thanks in advance. Ken Well, the tooth fairy didn't create it. Check your settings in Tools|Options|Backup. "ken" <noon...

Chart Axis #2
Using Excel 2003 I am charting some data that has a large range in numbers. I'm using a clustered column chart that compares values across categories. The chart has 4 categories of numbers to chart monthly. The problem I'm having is - in one category, the raw data numbers range from 2000 to 4500 and another category ranges from 25 to 90. When I chart this data, I cannot really see much of a bar in the chart for the smaller values. I want to at least have some bar showing. The minimum on the scale is zero. I thought to change the scale (Y Axis) minimum to a negative value and t...

Trouble with the template.
I want to reset the 'normal' template to default to "Times New Roman" 10point font, and the pages numbered centre top. This seems to work when I set and reopen Word2003 from the Administrator Template vis: C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates. But it will not work when I open Word2003 normally from the desktop. I have deleted the "Normal.dot" from the following and copied in the "Normal.dot" from the Administrator above: C:\Documents and Settings\All Users\Application Data\Microsoft\Templates...

Birthday reminder
Hi! We have a problem with the birthday reminder on the contact person. When we are creating a birthday reminder on a contact, all CRM users are getting a reminder in there calendar. Any out there who have the same problem? Best Regard Claus ...

creating a formul
Trying to create a formula to do the following: Sheet 1 column A a list of personal names a1-a10 Sheet 2 has list of names a1-a10 and list of dollar amounts colums d1-d10 want to search sheet one and if any name from sheet 2 found on sheet 1 than the corresponding dollar amount is entered. Any help appreciated. -- George I bet you want to use =vlookup(). Debra Dalgleish has some nice instructions at: http://www.contextures.com/xlFunctions02.html George A. Yorks wrote: > > Trying to create a formula to do the following: > Sheet 1 column A a list of personal names a1-a10 > ...

create pivot table from multiple sheet (excell 2003)
as understood, excell 2003 have row limit...but, currently i have data that more than the row limit... so, i split my data to 2 separate worksheet...the column name for each worksheet same.. my question, how can i create one pivot table from the two worksheet...i've tried using the wizard n consolidated data but failed.... thank you vm.. Maybe you could try creating the pivot directly from the Access source table (which doesn't have the 65k row limitations) In Excel, in a new sheet, Click Data > Import External data > Import data Navigate to where the Access file is >...

Creating New Profile?
I have Outlook 98 and would like to set up separate profiles for myself and my wife so that we can sync to separate pda's. The instructions in my manual say to open the Mai icon in the Control Panel which will open the "Service Properties" dialog box, but instead I get the "Internet accounts" dialog box which just enables me to add a new internet mail account. I never get to any of the other dialog boxs allowing me to "configure services" or "add services" The add services instructions say to select a profie (internet account profile- Contr...

charting calculated values
I am charting calculated values for each month Jan to Dec. The lin shows values for each completed month but then goes to zero for month where values have not yet been entered. How can the chart show only th values greater than zero -- dp ----------------------------------------------------------------------- dpj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=149 View this thread: http://www.excelforum.com/showthread.php?threadid=27225 Are the cells empty for future months? Under Tools/Options/Chart there are several options as to how to handle empty cells. ...

Chart source setting with VBA
Hi, I currently have a chart grpQuickLook on a form in Access. I am setting the row source of this based on the selection made by the user. If they select to add data by an individual, it will show the individuals chart and if they select to add data by a team, then it will show the team data. This is the code i have used ..grpQuickLook.RowSource = "SELECT tbl_Metrics_Data_Entry.dt_Date_Entered, Sum(tbl_Metrics_Data_Entry.intVolume) AS Volumes, " & _ "Sum(tbl_Metrics_Data_Entry.intTime) AS Timings FROM tbl_Met...

Lines on chart don't correlate to values being charted
I'm having a strange problem with a chart done in Excel 2003 (on a workbook prepared by someone other than myself, so I'm not sure of all that has happened with it before.) There are three lines drawn on the chart, and one is normal - that is, the line drawn correlates to the values being charted. But the other two lines don't correlate to the numbers they should be charting. When you hover over these two lines and it tells you the values for the data point, the data point values are fine - but that isn't what the line is drawing! For example, one series is charting ...

trouble with CrmIsapi.dll
I was experiencing the same problem with the SDK as many have posted to this group ... "the WhoAmI() call results in a http error 405 when running sample code" I followed some of the advice; checked my .srf mapping to CrmIsapi.dll and re-registered the CrmIsapi.dll. when I re-register CrmIsapi.dll I get an error 80004005 - unspecified. ??? I copied it to various places and retried it ... same result. I got the original from the disk and retried ... same result. unmapped it from .srf, restarted and retried to register it ... same result. Anyone experienced this. What's up with m...

Inserted images from file are showing blurry on screen, why?
Recently any image format I insert in Publisher 2007 appears blurry on screen. It looks fine in print preview, but it's difficult to know what your document will look like without printing. Why is this happening all of a sudden. Was always fine before. Puzzling. --- frmsrcurl: http://msgroups.net/microsoft.public.publisher/ Try; View | Pictures | Picture display...what do you have it set at? -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "Pam" wrote in message news:#NQ4N1QdKHA.2164@TK2MSFTNGP02.phx.gbl... > Recently any image for...

Is there any way to create a drop down calendar into a cell in ex.
I am trying to make an interoffice work order for maintenance issues and need to create a drop down calendar with a box with a button (icon) that when clicked on shows the calendar to pick the date and enter it into the box. I know how to insert a calendar that stays on the page but does not drop down. I am using Microsoft Excel 2003. Help!!!! See www.rondebruin.nl/calendar.htm -- HTH RP (remove nothere from the email address if mailing direct) "Heavensent1" <Heavensent1@discussions.microsoft.com> wrote in message news:71F54092-0CE9-43D7-ADDC-980E9ABF0227@microsof...

When inserting a image an outline shows instead of the photo
Using Word 2007, when inserting a photo into a Word document a place holder the size and shape of the photo show insead of the actual photo. How do I get the photo to show instead of just an outline. See http://sbarnhill.mvps.org/WordFAQs/InvisibleGraphics.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Sue M" <SueM@discussions.microsoft.com> wrote in message news:A7D66A1C-A9DB-4CEB-9493-2659D4619EBB@microsoft.com... > Using Word 2007, when inserting a photo into a Word document a place > ...

Weighted Average Charts by Volume: trendlines
Hi, I am trying to produce a chart with the following data: x-axis Y-Value Volume 10 1.95% 717 11 1.93% 519 12 2.31% 909 13 1.85% 866 14 1.95% 718 15 1.60% 810 16 1.60% 937 17 1.80% 890 18 2.10% 1572 19 1.05% 1333 20 0.99% 1520 Such that the 1.95 value at point 10 will occur 717 times, so that can plot a trendline through the points plotted in order that thi takes volume into account. Any ideas welcome??? Thanks, O -- Message posted from http://www.ExcelForum.com OJ - See what stats guru Jerry Lewis said about weighted linear regression in this post from January: http...