Create Line chart by Week

M

michung

New Member
Joined
Nov 19, 2014
Messages
17
Hi

How can I create a Line Chart by Week for the following data:

Date WTI Price US$
01-09-2014 94.96
02-09-2014 92.88
03-09-2014 95.54
04-09-2014 91.45
05-09-2014 93.29
06-09-2014 93.29
07-09-2014 93.29
08-09-2014 92.66
09-09-2014 90.01
10-09-2014 90.20
11-09-2014 89.54
12-09-2014 90.25
13-09-2014 90.25
14-09-2014 90.25
.....
.....
30-09-2014 91.50

The X-Axis Date type only allows Resolution by Date/Month/Year.

We would like to view the chart by Weeks.

We do NOT want to use the Days resolution where the major interval is 7.

Is there a method to get the chart represented by weeks?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
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?
 
Upvote 0
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
AB
11-Jan1
22-Jan1
33-Jan1
44-Jan1
55-Jan2
66-Jan2
77-Jan2
88-Jan2
99-Jan2
1010-Jan2
1111-Jan2
1212-Jan3
1313-Jan3
Sheet1
Cell Formulas
RangeFormula
B2=IF(WEEKDAY(A2)=1,B1+1,B1)
 
Last edited:
Upvote 0
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
AB

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4-Jan[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5-Jan[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6-Jan[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7-Jan[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8-Jan[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9-Jan[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10-Jan[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11-Jan[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12-Jan[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]13-Jan[/TD]
[TD="align: right"]3[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(WEEKDAY(A2)=1,B1+1,B1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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?
 
Upvote 0
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.

0194.96
0292.88
0395.54
0491.45
0593.29
0693.29
0793.29
0892.66
0990.01
1090.20
1189.54
1290.25
1390.25
1490.25
1592.92
1694.88
1794.42
1893.07
1992.41
2092.41
2192.41
2291.52
2391.56
2492.80
2592.53
2693.54
2793.54
2893.54
2994.57
3091.16

<colgroup><col width="79"><col width="79"><col width="79"><col width="79"><col width="79"><col width="79"></colgroup> <tbody>
[TD="width: 79"] DATE [/TD]
[TD="width: 79"] WK1[/TD]
[TD="width: 79"] WK2[/TD]
[TD="width: 79"] WK3[/TD]
[TD="width: 79"] WK4[/TD]
[TD="width: 79"] WK5[/TD]

</tbody>






 
Upvote 0
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
ABC
DATEWTIWK

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]94.96[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]92.88[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]95.54[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]91.45[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]93.29[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]93.29[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]93.29[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]92.66[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]90.01[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]90.2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]89.54[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]90.25[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]90.25[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]90.25[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]92.92[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]94.88[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]94.42[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]93.07[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]92.41[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]92.41[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]92.41[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]91.52[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]91.56[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]92.8[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]92.53[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]93.54[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]93.54[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]93.54[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]94.57[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]91.16[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(MOD(A2,7)=1,MAX(C$1:C1)+1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
:cool: 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
ABC
DATEWTIWK

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]94.96[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]92.88[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]95.54[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]91.45[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]93.29[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]93.29[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]93.29[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]92.66[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]90.01[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]90.2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]89.54[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]90.25[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]90.25[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]90.25[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]92.92[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]94.88[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]94.42[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]93.07[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]92.41[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]92.41[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]92.41[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]91.52[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]91.56[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]92.8[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]92.53[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]93.54[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]93.54[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]93.54[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]94.57[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]91.16[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(MOD(A2,7)=1,MAX(C$1:C1)+1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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...
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Line chart only showing 4 dates on X-Axis
Replies
3
Views
573
awoohaw
awoohaw
L
  • Question Question
How to create a detailed performance overview (Maybe a graph/chart)
Replies
1
Views
737
Laagi
L
S
  • Question Question
Macro to create a 'scatter chart with straight lines'
Replies
1
Views
586
saitken
S
N
  • Question Question
How can I create a pivot chart with two line graphs?
Replies
1
Views
1K
Jon Peltier
J
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P

Forum statistics

Threads
1,223,444
Messages
6,172,171
Members
452,445
Latest member
walkman99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top