Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2010
    Location
    Bristol, UK
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I'm not even sure that title is accurate, but here goes. I've got two columns: a set of transactions, each with a date, spanning four years. I want to display:
    • a line for each set of transactions
    • on a single chart
    • with a common X axis timeline of January - December

    This is so I can compare trends, and it's the X axis I'm having trouble with.

    I tried putting each year in its own column and then use the WEEKNUM function to get matching data for all four years, but there is different number of transactions in each year and its corresponding week, and common dates in each year only exist coincidentally, e.g. year 1 transactions start in week 28 but in all subsequent years they start in week 1.

    I feel like I'm missing something obvious with the original two column format here, so feel free to point out something obvious...

    Phil

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand what you want, you can create a formula to get the month of date. I would still keep it a date, using 1 as a day and then a year. I choose 2000 since it is a leap year. If we presume that the transactions start in A2 and the dates in B2, you can create the formula in C2 (and copy it down the list):

    =DATE(2000,MONTH(B2),1)
    [of course you can pick a different year and date]

    In D1:G1 you can enter the years to extract each year, and thus extract that data from Col B. I used in the attached file 2007-2010. Then in D2 you can enter:
    =IF(YEAR($B2)=D$1,$A2,NA())

    D2 can be copied into D2LG-whatever to fill all the years with the extracted data.

    Now the chart can be created with C1:G-Whatever. It will have the X in col C and the 4 data series, one for each year. The chart type is a line chart. in chart options - Axes, choose "Time-Scale" as the Category X-Axis, and format it Custom to (no quotes) "mmm" to display it as the 3-letter month

    If this is not like what you have or want, could you attach an example file of what you have and be more specific about what you want?
    Steve
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Aug 2010
    Location
    Bristol, UK
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Steve

    Thanks for the prompt response, and apologies for the delay in getting back to you, I posted just before I went away.

    I tried to apply your solution to my data, but the X axis isn't displaying correctly, as you can see in the attached file. My data is in columns A and B, and I'm wondering if part of the problem is that I have multiple payments in each month, whereas your example just had one per month (possibly because I didn't explain myself clearly). I want a Jan - Dec X axis to aggregate the timeline, but still have each transaction in its proper order.

    I hope that's clearer!

    Regards

    Phil
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Your X-Axis (and all your ranges) start with row 1. Since row 1 has a label, it confuses XL and it ignores your values and just uses 1,2,3,...3117 as the values. If you edit each range to start at row 2 you will get an X-axis more of what you asked for. It is a little "messy" since you have multiple transactions.

    You could create a few columns to make a pivot table and pivot chart to average the transactions for each month of each year.

    The attached fixes your chart, and creates the pivot data, table and chart

    Steve
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Aug 2010
    Location
    Bristol, UK
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Many thanks for this, it didn't occur to me to try averages but that gets me close to where I want to be.

  6. #6
    New Lounger
    Join Date
    Aug 2010
    Location
    Bristol, UK
    Posts
    15
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Just to finish this off, I turned to Google and found what I was looking for. In doing so, I realised that I forgot to point out that I'm using Excel 2002 (which I neglected to mention here, sorry...), and at work I use a more recent version with the WEEKNUM (week number) function, which is what started me off on this.

    So for users with earlier versions of Excel, a formula that calculates WEEKNUM is here, one that calculates a day number (which I'm not sure Excel has in later versions) is here.

    Thanks for your help.

    Phil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •