+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Daily, weekly, monthly chart to put in graph

  1. #1
    Registered User
    Join Date
    07-05-2011
    Location
    sdgsgdfg
    MS-Off Ver
    Excel 2007
    Posts
    3

    Daily, weekly, monthly chart to put in graph

    Hello,

    I have checked other threads before posting here, but even though I found lots of information, nothing was truly adapted to my situation.

    Here is my problem:

    I extract from a huge data sheet thousands rows of sales informations.
    Using a pivot chart, I regroup those in a simple chart.

    Then, from the pivot chart, I regroup my products by line and show the sales per day.
    The chart looks pretty much like this:

    01/06 02/06 03/06 04/06 05/06
    orange 10 12 13 14 9
    banana 2 0 6 4 8
    kiwi 6 11 12 5 10

    Ok, so far, all's good. Exporting this to a graph is piece of cake.

    Now, I want to regroup those data in weekly and monthly sales. I have tried many methods, even merged cells (don't use merge cells!! I knowwwww), but nothing works.
    Here is the concept:


    A1 B1 C1 D1 E1 F1
    01/06 02/06 03/06 04/06 05/06 06/06
    orange 10 12 13 14 9 0
    banana 2 0 6 4 8 5
    kiwi 6 11 12 5 10 8
    week 1 week 2
    orange 212 320
    banana 32 60
    kiwi 156 49
    month 1
    orange 652
    banana 452
    kiwi 365

    I have tried to organize it in a logical way: the week goes under each sunday, and the month under each first day of the month... logical, right?
    Then I put all this in a graph... Problem is: for weeks, cells A1 and B1 are empty (in fact only one cell is full every 7 cells, to be really logical). Result: my graph shows one bar, six gaps, one bar, six gaps, etc...
    Same for months (of course).

    So, I tried merged cells... but as expected, even though "A1:C1" becomes just one cell... the graph still see several: problem not solved.

    I have also tried to create a separate table: my weeks are not aligned with the days, but whatever, it should work, right?
    No, it doesn't. Here is the table:

    A50 B50 C50 C50
    week1 week2 week3 week4
    orange 510 112 413 614
    banana 22 50 26 504
    kiwi 206 111 12 125

    Alright... making a graph out of it: easy.
    Problem: getting those data in this chart!

    How do I do. I say:

    A50 = sum(A1:G1) means the sum of all the sales for this week.
    Ok... then I do the same for B50:
    B50 = sum(H1:O1) ok, still good.

    Now, I drag my formula to have the same system everywhere... right. But it doesn't work.
    Excel will copy this:

    C50 = sum(I1:P1)
    D50 = sum(J1:Q1)
    etc...
    where it should be:
    C50 = sum(P1:V1)
    D50 = sum(...)

    Therefore, I do not get proper results, and, indeed, not proper graph.

    If anyone had a solution, I would be very grateful.
    If anything isn't clear, please let me know.

    Thank you very much.

    Edit: sorry my tables aren't clear, but I can't find out how to draw nice table on the website.
    edit-edit: I added a picture in attachment for the table
    Attached Images Attached Images
    Last edited by jokerag; 07-05-2011 at 03:53 AM. Reason: adding picture

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,462

    Re: Daily, weekly, monthly chart to put in graph

    If your column field is a date then you should be able to group by date.

    If not you may need to add another field or 2 to you source data table.

    A sample workbook would be more helpful if you need more help.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    07-05-2011
    Location
    sdgsgdfg
    MS-Off Ver
    Excel 2007
    Posts
    3

    Exclamation Re: Daily, weekly, monthly chart to put in graph

    Hello Andy Pope,

    Thank you very much for your answer.

    I have created a file, let me explain.

    First part: Sales per day. Easy. Simply calculate the sum of products and get the total, put this in a line chart, all good.

    Second part, sales per week. Still ok, calculate the sum of each product for the past 7 days, apply the formula to all weeks, all good.

    Now, the problem stands here:

    As you can see, I try to put those weekly results in a chart. I have created two as example: one automatic, one manual.

    The automatic, I select all lines (Sales per week - to get the date, Orange/banana/kiwi - to get the figures, Total - to see the total evolution) and generate a bar chart.
    For manual, I select only the columns with value (one column every 7), and generate a chart.

    Now, here is the problem: automatic is convenient, but gaps appear. I can't find out how to get rid of those gaps. Here, it is still acceptable, but image now that instead of 2 weeks I have 20.... the gaps take over 80% of the space so that the results are unreadable.
    Manual gives the result I want. Problem: I can't select manually. Here is ok, just two weeks. Now imagine it goes to 10, 20, 100 weeks... I can't play like this.

    So, my question is (long story, short question): how to get rid of those gaps giving me headache?

    Thanks in advance
    Attached Files Attached Files

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,462

    Re: Daily, weekly, monthly chart to put in graph

    I thought you said you had a pivot table. That is just a bunch of cells.

    In order to remove the gaps create a weekly summary table

    In C9 use
    =SUM(OFFSET($C4,0,(COLUMN(C4)-COLUMN($C$3))*7,1,7))

    Drag across and down.

  5. #5
    Registered User
    Join Date
    07-05-2011
    Location
    sdgsgdfg
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Daily, weekly, monthly chart to put in graph

    I tried the offset, but couldn't have it work! You are a wizzard! haha

    Thanks, anyway.

    And, yes, in my real chart, I take it from pivot chart, but here, for demo, I used simple cells.

    The thing is that i couldn't ever extract stuff from my pivot chart, so I gave up, and instead, just copy past the content of the pivot chart in a fix one...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1