Create Line chart by Week

Note that I do NOT want to group by week and sum by WTI per week. Instead of seeing the 7 days per week in the x-axis, I would like to see Week# and the corresponding 7 points on the line chart which represents the 7 days of the week. It is a bit difficult when viewing the x-axis with the 30-31 days. The use of the Days resolution where the major interval is 7 is breaking the dates into 01, 08, 15, 22, 29. Is there any way to showing the week # instead of day?

This is because Excel thinks it is smarter than you, I have just been through the very same thing myself.

My way around it was to make a helper column referencing the date with a formula for example =TEXT(A1,"DD/MM/YYYY") or whatever format you want and then chart on that and your data, then it will plot the points without trying to add days between.

Edit: Oh wait, you want to sum based on your week?

Similar concept but use this formula:

Hi Blade

Thanks for the response. Previously what I did was entered the week number manually per row. However, the chart x-axis is displaying 1 1 1 1 2 2 2 2 2 2 2 3 3 3 3 ... etc.
Is there any way to just show 1 2 3 4 .... etc with the corresponding WTI?

Regards
Michelle

Blade Hunter said:

This is because Excel thinks it is smarter than you, I have just been through the very same thing myself.

My way around it was to make a helper column referencing the date with a formula for example =TEXT(A1,"DD/MM/YYYY") or whatever format you want and then chart on that and your data, then it will plot the points without trying to add days between.

Edit: Oh wait, you want to sum based on your week?

Similar concept but use this formula:

Excel 2010

	A	B
1	1-Jan	1
2	2-Jan	1
3	3-Jan	1
4	4-Jan	1
5	5-Jan	2
6	6-Jan	2
7	7-Jan	2
8	8-Jan	2
9	9-Jan	2
10	10-Jan	2
11	11-Jan	2
12	12-Jan	3
13	13-Jan	3

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =IF(WEEKDAY(A2)=1,B1+1,B1) <tbody> </tbody>

<tbody>
</tbody>

Click to expand...

So do you want to sum the WTI per week or show the week on the X axis on only the first daily WTI on the chart for that week?

Hi Blade.

No... I do not want to sum the WTI per week.

What I would like to see on the chart is:

X-axis =

week 1 plot against WTIs

94.96
92.88
95.54
91.45
93.29
93.29
93.29

week 2 plot against WTIs

92.66
90.01
90.20
89.54
90.25
90.25
90.25
.....

I just don't want to see the week repeating on the x-axis.

I've tried creating a series table. The only thing I did not like with this is that there is a break in the linear line per week ... but this was the closest where I can show a representation of the weekly trend of WTI. The main concern was that with the original table with the 30 days on the x-axis made the chart look too cluttered ... so I was hoping to show by weeks 1 2 3 4 ... for better presentation instead of the 1 to 30 days on the x-axis.

OK so the easy option as I see it is where you have a formula to populate a column with the week number just wrap it in a formula so if the result is the same as above then display "" otherwise display the result.

It will give you something like this:

Excel 2010

Change =1 to a =4 to move the label to the middle of the column, then in your axis options set your tick marks to every 7

Thanks Blade ... I did the same thing (without the formulas though) to get the week to show only once. Was not sure what I was doing was right though although it made my presentation better.

I would try using the two functions you have provided, and try creating a dynamic monthly chart at the same time.

Hmmm .... to make it even more complex, is there a way to get a vertical line drawn on the chart at the end of a week or maybe colour code by week?

The user asked if a line can be drawn at the end of the working week / and weekend or public holiday. I know that this is much more complex in the design of the chart. WTI prices are only given on working days except weekends or public holidays ... thus we tend to use the WTI figure from the previous day. The easy solution here I can think of is using draw objects to separate the weeks ... weekends. Any suggestions to automate this?

Blade Hunter said:

OK so the easy option as I see it is where you have a formula to populate a column with the week number just wrap it in a formula so if the result is the same as above then display "" otherwise display the result.

It will give you something like this:

Excel 2010

	A	B	C
1	DATE	WTI	WK
2	1	94.96	1
3	2	92.88
4	3	95.54
5	4	91.45
6	5	93.29
7	6	93.29
8	7	93.29
9	8	92.66	2
10	9	90.01
11	10	90.2
12	11	89.54
13	12	90.25
14	13	90.25
15	14	90.25
16	15	92.92	3
17	16	94.88
18	17	94.42
19	18	93.07
20	19	92.41
21	20	92.41
22	21	92.41
23	22	91.52	4
24	23	91.56
25	24	92.8
26	25	92.53
27	26	93.54
28	27	93.54
29	28	93.54
30	29	94.57	5
31	30	91.16

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =IF(MOD(A2,7)=1,MAX(C$1:C1)+1,"") <tbody> </tbody>

<tbody>
</tbody>

Change =1 to a =4 to move the label to the middle of the column, then in your axis options set your tick marks to every 7

Click to expand...

There is, I can't exactly remember how but from memory you make it another series on the chart. I will see if I can dig something up on it. I think I might have even made it a number that was bigger than my max Y axis then made it a really really thin bar on just that series.

Okay. Tomorrow then... It's already 11:33 pm over here ... time for some zzzz's. Thank you again.

Blade Hunter said:

There is, I can't exactly remember how but from memory you make it another series on the chart. I will see if I can dig something up on it. I think I might have even made it a number that was bigger than my max Y axis then made it a really really thin bar on just that series.

Click to expand...