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

**jokerag** · 07-05-2011, 03:37 AM

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

**Andy Pope** · 07-05-2011, 03:52 AM

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.

**jokerag** · 07-06-2011, 10:00 PM

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

**Andy Pope** · 07-07-2011, 04:28 AM

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.

**jokerag** · 07-07-2011, 04:58 AM

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...