My workmate has recently got me into going for lunchtime runs around the Refinery.
Running on sand is hard. As a person who has only ever run on the road, I found it very difficult to adjust.
I haven’t quite picked when yet, but sometimes the sand is really soft. It’s like running in quicksand.
After work, I decided to knock up a few Excel Charts to give me more information before my run.
It’s not really going to change my habits, but I feel easier knowing what I’m in for. OK, maybe I like a tailwind.
I’ve got realtime data for Tide Height, Wind Speed and Wind Direction.
Tide Height and Wind Speed I’ve done as standard Line Charts, but Wind Direction didn’t look quite right as a Line Chart so I decided to do something different.
15 minute averages extend for about 24 columns – 6 hours worth.
The formulas – a bit of Trig for the end point coordinates.
The charts you see in the snapshot are Scatter Charts.
Steps to create a chart:
Select B8:B9. Hit the AutoChart button
Select the “Scatter with data points connected by lines without markers”. Click Next
Select the Series tab. For X Values select B6:B7. Click Finish
Select the Legend box and hit the Delete key to destroy it
Doubleclick the Y Axis to bring up the “Format Axis” window. Select the Scale tab
Set Minimum to -1 and Maximum to 1. Click OK. Hit the Delete key to destroy the Y axis
Repeat for the X Axis
Select the Gridlines and hit the Delete key to destroy it
Doubleclick the Plot Area to bring up the “Format Plot Area” window
Set the border to None and the Area to None. Click OK
Doubleclick the line. Set the Line Color to Black. Click OK
The line should still be selected. Note that two blobs are at the ends of the line.
Click the blob near the center of the chart. Then, doubleclick the blob
In the Marker section, set the Style to Circle, the Foreground to Black and the Size to 5. Click OK
Now you need to resize the chart so it fits in the width of one column. The top-left of the chart should start in cell B11.
This takes some fiddling since the Chart Area doesn’t want to play nice with the Chart Object which contains it.
Set the Zoom to 200% if it helps (but make sure you set it back to 100% afterward!)
I didn’t sit there adding all of the charts, I just created the left-most one and copied them across.
But, it’s not quite as simple as a copy/paste. A normal copy/paste would have all the charts still looking at column B.
Charts can not be relative.
That is, you can’t move the chart over a column and expect it to now look at column C.
Ranges do it fine, you just remove the $ and that’s relative.
It appears that charts can’t use Relative References. It’s Absolute References only.
Here is why VBA is my friend.
While copying the original chart, it finds the column part of the Chart formula and adjust it to look at the destination column.
Make sure the Chart is selected, then run this macro:
Const cColumns = 23
Dim i As Long, cob As ChartObject
Dim str As String, strCol As String, lngCol As Long
Select Case TypeName(Selection)
Case “PlotArea”, “ChartArea” ‘ensure the chart has been selected first
Set cob = Selection.Parent.Parent
strCol = cob.Chart.SeriesCollection(1).Formula
i = InStr(1, strCol, “$”) ‘look for the column part of the =SERIES() formula
If i > 0 Then
strCol = Mid(strCol, i + 1, 2) ‘ take the AB out of “$AB123:…”
If Not (Right(strCol, 1) >= “A” And Right(strCol, 1) <= “Z”) Then strCol = Left(strCol, 1)
lngCol = Range(strCol & “1”).Column
For i = 1 To cColumns
str = Split(Columns(lngCol).Offset(, i).Address, “:”)(0) ‘get the “AC” bit from a column number
.Formula = Replace(.Formula, “$” & strCol, str) ‘replace the old $AB with $AC
MsgBox “The source chart must first be selected”, vbExclamation, “Selection Error”
Finally, you can pretty it up by hiding the Coordinates. Set the Number Format for Rows 6:9 as ;;; (3 semi-colons)