Trying to compare % to date across several years

I have a simple need.  I have calculated cummulative % of 
full year's sales for several years and want to compare 
the sales patterns.  The chart would show each year's 
sales from 0% up to 100% with % as Y axis and day of year 
as X axis.  Can't make any date formats allow such a 
comparison, so I have calculated a day of year by 
subtracting the date of each sale from the 12/31 date of 
the prior year, therefore each data point is a value 
between 0 and 100% and a "date" between 1 and 365.  Seems 
like a routine chart, but nothing seems to make the x 
axis display correctly.

Surely this is a common need?  Would appreciate any help.
0
8/3/2004 10:00:13 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
470 Views

Similar Articles

[PageSpeed] 53

Richard -

How is your X axis displaying? How would you like it to display?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Richard wrote:

> I have a simple need.  I have calculated cummulative % of 
> full year's sales for several years and want to compare 
> the sales patterns.  The chart would show each year's 
> sales from 0% up to 100% with % as Y axis and day of year 
> as X axis.  Can't make any date formats allow such a 
> comparison, so I have calculated a day of year by 
> subtracting the date of each sale from the 12/31 date of 
> the prior year, therefore each data point is a value 
> between 0 and 100% and a "date" between 1 and 365.  Seems 
> like a routine chart, but nothing seems to make the x 
> axis display correctly.
> 
> Surely this is a common need?  Would appreciate any help.

0
8/4/2004 1:55:12 AM
Originally I wanted the X axis to be month/day 
(regardless of year) but could find no way to do that.  
So I calculated day of year (from 1 to 365).  I tried 
various ways to set that up which seems simple, but got a 
whole set of odd results ranging from things like 1/11, 
1/19 etc (not relating to the month/day); to a single 
number (seems random) to odd sets of other numbers.

The original data are in two columns:  one with the % 
value and one with the day of the year.  Each year's data 
is together in groups, one above the other.  And, of 
course, the random dates of each large sale mean that 
each year's dates are different from the other years'.

>-----Original Message-----
>Richard -
>
>How is your X axis displaying? How would you like it to 
display?
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>Richard wrote:
>
>> I have a simple need.  I have calculated cummulative % 
of 
>> full year's sales for several years and want to 
compare 
>> the sales patterns.  The chart would show each year's 
>> sales from 0% up to 100% with % as Y axis and day of 
year 
>> as X axis.  Can't make any date formats allow such a 
>> comparison, so I have calculated a day of year by 
>> subtracting the date of each sale from the 12/31 date 
of 
>> the prior year, therefore each data point is a value 
>> between 0 and 100% and a "date" between 1 and 365.  
Seems 
>> like a routine chart, but nothing seems to make the x 
>> axis display correctly.
>> 
>> Surely this is a common need?  Would appreciate any 
help.
>
>.
>
0
anonymous (74722)
8/4/2004 2:34:57 PM
Richard -

I think we can fake it. You might be off � a day depending on leap year, 
but let's see how it looks.

Put dates for your first series in the first column, and values for the 
first year in the second. Put the values for subsequent years in the 
next columns. Use the years for the column headers.

            2001   2002   2003   etc.
1/1/2001    20     22     21
1/2/2001    25     27     28
1/3/2001    28     29     33
1/4/2001    31     31     34
etc.

Keep the top left cell blank. Select the range and make your Line chart. 
The first series' dates provide the ability to format the date as 
month/day (use a custom number format of "mm/dd" or "mmm/dd").

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

anonymous@discussions.microsoft.com wrote:
> Originally I wanted the X axis to be month/day 
> (regardless of year) but could find no way to do that.  
> So I calculated day of year (from 1 to 365).  I tried 
> various ways to set that up which seems simple, but got a 
> whole set of odd results ranging from things like 1/11, 
> 1/19 etc (not relating to the month/day); to a single 
> number (seems random) to odd sets of other numbers.
> 
> The original data are in two columns:  one with the % 
> value and one with the day of the year.  Each year's data 
> is together in groups, one above the other.  And, of 
> course, the random dates of each large sale mean that 
> each year's dates are different from the other years'.
> 
> 
>>-----Original Message-----
>>Richard -
>>
>>How is your X axis displaying? How would you like it to 
> 
> display?
> 
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Richard wrote:
>>
>>
>>>I have a simple need.  I have calculated cummulative % 
> 
> of 
> 
>>>full year's sales for several years and want to 
> 
> compare 
> 
>>>the sales patterns.  The chart would show each year's 
>>>sales from 0% up to 100% with % as Y axis and day of 
> 
> year 
> 
>>>as X axis.  Can't make any date formats allow such a 
>>>comparison, so I have calculated a day of year by 
>>>subtracting the date of each sale from the 12/31 date 
> 
> of 
> 
>>>the prior year, therefore each data point is a value 
>>>between 0 and 100% and a "date" between 1 and 365.  
> 
> Seems 
> 
>>>like a routine chart, but nothing seems to make the x 
>>>axis display correctly.
>>>
>>>Surely this is a common need?  Would appreciate any 
> 
> help.
> 
>>.
>>

0
8/4/2004 6:49:50 PM
Thanks.  Using your idea, after an extreme amount of=20
manipulation and sorting, I was able to make a graph for=20
which I am grateful.  This was a substantial=20
disappointment for me in Excel's graphing.  It is=20
requiring that all of the data points be ordered within a=20
constant x axis order, rather than reading the x axis=20
point within a column to correspond with that particular=20
y axis data.

I appreciate your help.=20

Richard


>-----Original Message-----
>Richard -
>
>I think we can fake it. You might be off =B1 a day=20
depending on leap year,=20
>but let's see how it looks.
>
>Put dates for your first series in the first column, and=20
values for the=20
>first year in the second. Put the values for subsequent=20
years in the=20
>next columns. Use the years for the column headers.
>
>            2001   2002   2003   etc.
>1/1/2001    20     22     21
>1/2/2001    25     27     28
>1/3/2001    28     29     33
>1/4/2001    31     31     34
>etc.
>
>Keep the top left cell blank. Select the range and make=20
your Line chart.=20
>The first series' dates provide the ability to format=20
the date as=20
>month/day (use a custom number format of "mm/dd"=20
or "mmm/dd").
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>anonymous@discussions.microsoft.com wrote:
>> Originally I wanted the X axis to be month/day=20
>> (regardless of year) but could find no way to do=20
that. =20
>> So I calculated day of year (from 1 to 365).  I tried=20
>> various ways to set that up which seems simple, but=20
got a=20
>> whole set of odd results ranging from things like=20
1/11,=20
>> 1/19 etc (not relating to the month/day); to a single=20
>> number (seems random) to odd sets of other numbers.
>>=20
>> The original data are in two columns:  one with the %=20
>> value and one with the day of the year.  Each year's=20
data=20
>> is together in groups, one above the other.  And, of=20
>> course, the random dates of each large sale mean that=20
>> each year's dates are different from the other years'.
>>=20
>>=20
>>>-----Original Message-----
>>>Richard -
>>>
>>>How is your X axis displaying? How would you like it=20
to=20
>>=20
>> display?
>>=20
>>>- Jon
>>>-------
>>>Jon Peltier, Microsoft Excel MVP
>>>Peltier Technical Services
>>>Tutorials and Custom Solutions
>>>http://PeltierTech.com/
>>>_______
>>>
>>>Richard wrote:
>>>
>>>
>>>>I have a simple need.  I have calculated cummulative=20
%=20
>>=20
>> of=20
>>=20
>>>>full year's sales for several years and want to=20
>>=20
>> compare=20
>>=20
>>>>the sales patterns.  The chart would show each year's=20
>>>>sales from 0% up to 100% with % as Y axis and day of=20
>>=20
>> year=20
>>=20
>>>>as X axis.  Can't make any date formats allow such a=20
>>>>comparison, so I have calculated a day of year by=20
>>>>subtracting the date of each sale from the 12/31 date=20
>>=20
>> of=20
>>=20
>>>>the prior year, therefore each data point is a value=20
>>>>between 0 and 100% and a "date" between 1 and 365. =20
>>=20
>> Seems=20
>>=20
>>>>like a routine chart, but nothing seems to make the x=20
>>>>axis display correctly.
>>>>
>>>>Surely this is a common need?  Would appreciate any=20
>>=20
>> help.
>>=20
>>>.
>>>
>
>.
>
0
anonymous (74722)
8/5/2004 2:36:57 PM
Reply:

Similar Artilces:

AA error when trying to print transactions
We're trying to print a batch of transactions that has no analytical accounting codes attached to them. GP is returning the error message "Violation of PRIMARY KEY constraint 'PKAAG60001'. Cannot insert primary key in object 'AAG60001'..." What insert is being attempted here? And if there are no Analyticals attached, why is an AA constraint being referenced? Thanks for your help! I'm not sure if this will help. The key referenced is built using fields aaGLWorkHdrID and aaGLWorkDistID -- Richard L. Whaley Author / Consultant / MVP http://www.Accolade...

Notification Email on duration date
Hi I have a question that is there any way to send email to owner when their task is reaching due date set for the task. I tried to make it work with workflow but I couldn't. Also I read through all the post here and couldn't find anything relating to my question. Sorry, subject should be "Notification Email on due date", note "duration date" Thks, motoC "motoC" wrote: > Hi > > I have a question that is there any way to send email to owner when their > task is reaching due date set for the task. > > I tried to make it work with...

Date calcutation
Good afternoon, i need some help with the following: I have a maintenace DB, i have the following fields MachineHours (long int), MaintenaceHours (long int), MaintenaceDate (date) and WeeklyHourWork (long int). I have a text box NextMaintenace (date) where i calcute the date of the next maintenace based on the date of the maintenace (MaintenaceDate) difference between MachineHours, MaintenaceHours and WeeklyHourWork. example: if MachineHours=4000, MaintenaceHours=7000, MaintenaceDate=26-10-2007, WeeklyHourWork=40 then NextMaintenace=15-03-2009 This works fine but I need to put a limit to...

Date Stamp
I've used this Macro and need to tweak it: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub If Not Intersect(Range("a10"), .Cells) Is Nothing Then Application.EnableEvents = False With Me.Range("b10") .NumberFormat = "dd mmm yyyy" .Value = Now End With Application.EnableEvents = True End If End With End Sub This macro stamps B10 if something is entered in A10 but I also want this t...

Message of: a program is trying to access email addresses
you have stored in outlook. Do you want to allow this. Why does this show up when a MAPI account tries to use this. It there something in a security setting I can change? Thanks. See if the information on the following page helps: http://www.slipstick.com/outlook/esecup.htm#autosec -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "Kim Newberry" <olgkim@tampabay.rr.com> wrote in message news:004901c34d57$c602c670$a601280a@phx.gbl... > you...

Dynamic Range Charts Across Worksheets?
Hi, I'm trying to create a graph of a dynamic range of cells. In order t do this, I defined two named functions (X and Y) to graph. Everythin works great, except that I need to do this for a lot of different set of data on different worksheets. The parameters for each worksheet ar the same, but I'm trying to find a way to get around defining a ne named function for every single worksheet. Is there some way to set the named function to refer to the workshee that the graph is located in, instead of a specific worksheet? Thanks -- Message posted from http://www.ExcelForum.com A few ...

Comparing data
There are 2 column in this query:- Column 1 Column 2 A A B C C E D D I want this 2 column to compare. If both A, display A, if different compare next 1...However, the result of the third column will give me all A,C,D without B and E included at the last row? Any idea to make them show all A,C,D,B,E? -- Message posted via http://www.accessmonster.com On Mon, 21 May 2007 02:19:56 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >There are 2 column in this query:- > >...

ActiveSync 3.7 doesn't stay up to date with Outlook 2003
I just recently upgraded to Outlook 2003 (from XP) and I've found that my Pocket PC won't stay current with emails while it's connected to my PC in the cradle. I've noticed the problem when I delete emails from my inbox on the PC or I move emails to a folder on the PC. I would expect ActiveSync to fire up and update the Pocket PC but it doesn't seem to recognize that anything has changed. However, if I disconnect the Pocket PC from the cradle and reconnect it seems to recognize the differences and get itself up to date. Has anyone seen this problem? Any ideas o...

Yearly tick marks on the x axis
How do you show tick marks each 12 months in a graph when the data is from month 1 to say month 60. If you say want a tick mark each 12 th division you have 1,13,25,37 etc. You can't start from zero so how do you do this I'm using Excel 2002 Thanks Ken G Ken, I tried this using a Line chart (as opposed to an XY, or Scatter, chart) and it worked fine. I defined the x-axis as a range containing the numbers 1 to 60, and the y-axis the adjacent cells in the next column. Double clicking on the x-axis to open the Format Axis dialog, I selected the Scale tab. The first box (Y-axis cros...

compare two tables
For testing purposes I generated (2) identical tables using (2) different methods. Somehow, one table came out with 18 more records. How can I write a query that would generate those 18 extra records? -- TIA On Thu, 27 Dec 2007 22:22:00 -0800, tsison7 <tsison7@discussions.microsoft.com> wrote: >For testing purposes I generated (2) identical tables using (2) different >methods. Somehow, one table came out with 18 more records. > >How can I write a query that would generate those 18 extra records? Create a new Query. Select the "Unmatched Query Wizard". ...

Chinese New Year 2004
Outlook lists Feb 20, 2004 as the 1st day of the Chinese New Year. It is actually Jan 22, 2004. How do I correct this error? ...

SQL Query to transform/group data by Date
Hi, I have a large Access table with data organised as follows: Field1: Code Field2: Date Field3: Value1 Field4: Value2 There are seveal different codes and therefore duplicate dates. I'd like to run a query to bring back each code grouped by date and so put the codes along the top as feilds. For example the query below brings back the following data for two codes. SELECT field2, field1, field3 FROM Data WHERE field1 In ('LLOY','RSA'); 21/01/05, LLOY, 12454 22/01/05, LLOY, 31541 21/01/05, RSA, 21241 22/01/05, RSA, 12414 Instead I want the data to look like this: ...

date in a text cell
I need to show my date as 06-Dec-2005 in a text cell. Is it possible to have a regular date format in a text cell. Thanks If you format the cell as Text, then you'll have to type in what you want--exactly the way you want to see it. If you don't format it as text, you could type the date in anyway that's a date, then use a custom format of: dd-mmm-yyyy (format|cells|number tab|custom category) Dajana wrote: > > I need to show my date as 06-Dec-2005 in a text cell. Is it possible to have > a regular date format in a text cell. > > Thanks -- Dave Peterso...

Dynamics GP routing link several BOM's at once
When using routing links allow selection of several items at once to link to a routing step instead of one at a time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=487897...

Append Query
I have a table "Rates" with the following data: StartDate: 5/15/08 End Date: 6/23/08 Rate: $53 StartDate: 6/24/08 End Date: 7/15/08 Rate: $86 StartDate: 7/16/08 EndDate: 9/19/08 Rate: $99 I have another table "Transactions". I want to create an append query such that if I supply the StartDate and EndDate, the query will populate the "Transactons" table with date and rate data from the "Rates" table for each and every day within the date range. Example: If I supply the StartDate of 6/23/08 and the EndDate of 6/25/08, the query would pop...

Changing start date
Scenario: In OL 2003, I have an event that's two weeks long, starting June 1 and ending June 14. I want to change the start date to May 27 but leave the end date alone. If I change the start date, OL automatically changes the end date to preserve the two-week duration. Is there a way to change the start date without affecting the end date? Richard Evans <infodex@mindspring.com> wrote: > In OL 2003, I have an event that's two weeks long, starting June 1 and > ending June 14. I want to change the start date to May 27 but leave > the end date alone. If I change the s...

Posting new data to PM & RM before year-end or fiscal period close
Hi GP Users, If I have already posted some new data before closing the old financial year, what are the possible problems that may happen when I close it? Any solutions to that? Thanks Andrew ...

CONVERT TIME & TRIM DATE
I need help converting time to an AM/PM time format our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. The date displays: 2007-03-09 00:00:00.000, how do I have it display the date as 03-09-2007? Thanks. Specify the display format of the control or field: mm-dd-yyyy RENEE705 wrote: >I need help converting time to an AM/PM time format >our database displays time in 4 digits : 0006, 0737, 1217, 2149. I am >finding that 0006 is 12:06 AM and 1217 is 12:17 PM, etc. >The date displays: 2007-03-09 00:00:00.0...

Analysing data from several excel workbooks
Hi, I'm totally new to excel and i need to analyse the changes in a funding position across several years in a seperate excel sheet. As in, submissions are made year on year showing the amount of revenue spent on several different services. I need to be able to compare how those have moved over the last 5 years in a single spreadsheet. I appreciate any help. Thanks To give a clear answer we would need a bit more detail of the data layout Give us a simplified version of what the data looks like Your 'subject' talks about different workBOOKS but the text of the ...

help with range lookup and date criteria
Hello, I have a list of doctors (column A) that are each on-cal through a number of days. My argument is: if the date value of B1 an C1 is within the current date, then repeat the value of A1 -- James Spaldin ----------------------------------------------------------------------- James Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39088 hi, James ! > ... a list of doctors (column A) that are each on-call through a number of days. > ... argument is: if the date value ...

what affect does the budget effective date have
when you save a budget money asks for the efective dates from - to. Do these have any purpose? it does not appear to chagne the way that money displays the budget and even if you go past hte budget time it will continue using the old budget amounts. If you try to have it re-calculate the past information it does not. it seems as though these are not ment for anything. ...

CFtpFileFind return null creation date
Hi All I am trying to list the URL and timestamp of some files on a server in a list box in a simple dialog using FTP. The code is as follows: void CFTPTestDlg::OnTest() { CListBox* pbox = (CListBox*)GetDlgItem(IDC_LB_FILES); pbox->ResetContent(); // Create session object to initialise libs CInternetSession sess(_T("My Test")); // Declare a FTPConnection CFtpConnection* pftpConn = NULL; try{ pftpConn = sess.GetFtpConnection("ftp.,mytestsite.co.uk", "mylogon","mypassword"); if (!pftpConn->SetCurrentDirectory("/Testdir/T...

extract year from Date Value
Good morning, Could someone help me extract the year portion from a date value such as this 11/20/2009? Thanks in advance, Mike With the Date in A1, place =YEAR(A1) in B1 Takeadoe wrote: > Good morning, > > Could someone help me extract the year portion from a date value such > as this 11/20/2009? > > Thanks in advance, > > Mike ...

can you date time stamp entries in excel
is it possible that when someone puts an entry on a shared worksheet in excel it can automatically date and time stamp their entry. Private Sub Worksheet_Change(By Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target .Offset(0, 1).Value = Format(Now, "dd mmm yyy hh:mm") End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate ...

overlaping dates in two files
MS Money 2001 deluxe on Windows XP Pro I archived my 2004 accounts and never balanced them. I decided i needed to and now have two files, both .mny files with 2003Archive.mny is from 1/2002 to 12/2004 Active2005.mny is from 1/2004 to present. I'd like to combine them into one file so I can balance the accounts (three main accounts) because my statements are not ending nicely on the end of the month. Please recommend the best method so I can balance the accounts. I think I need to conbine the files or somehow get the months that are now balanced in the 2003Archive.mny file to give...