Wind Direction Chart

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.
B6: 0
B7: =COS(B4/180)
B8: 0
B9: =SIN(B4/180)

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:

Sub CopySelectedChartOver()
    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
                    cob.Copy
                    cob.TopLeftCell.Offset(, i).Select
                    str = Split(Columns(lngCol).Offset(, i).Address, “:”)(0) ‘get the “AC” bit from a column number
                   ActiveSheet.Paste
                    With Selection.Parent.SeriesCollection(1)
                        .Formula = Replace(.Formula, “$” & strCol, str) ‘replace the old $AB with $AC
                   End With
                Next
            End If
 
        Case Else
            MsgBox “The source chart must first be selected”, vbExclamation, “Selection Error”
    End Select
End Sub

Finally, you can pretty it up by hiding the Coordinates. Set the Number Format for Rows 6:9 as ;;; (3 semi-colons)

Posted in Uncategorized

13 thoughts on “Wind Direction Chart

  1. Rob –

    This is cool stuff. I’ve done a bunch of projects like this. For example, this speedometer chart:

    http://peltiertech.com/Excel/Charts/SpeedometerXP.html

    You can make it a bit fancier if you use the wind speed as a multiplier for the trig formulas, so that the length of the plotted line is proportional to the wind speed.

    For changing the series of each additional chart, try this little utility:

    http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

    – Jon

  2. Jon,

    It was your speedo which enabled me to think about using trig with scatter line charts ;)

    One thing I like about the “chart per column” idea is that you can use the window horz scrollbar and the chart looks animated.

    Rob

  3. What units of measurement are in cell B4 through E4?

    If they are in degrees, then the formula in cell B7 should be COS(B4 * 3.1416[pi]/180).

    I wrote some VBA code that would graphically indicate direction using the drawing objects (arrowhead line) on it. If you want it I’ll post it.

  4. Murray,

    You are correct. I must have been looking at a previous version.

    What I settled on was =COS(B4 * PI() / 180)

    Please post your code! it’s great to see other examples:
    When posting code in this comment box, replace:
    ” with &quot;
    & with &amp;
    < with &lt;
    > with &gt;

  5. Sub Direction()



    Dim ang As Double
    Dim length As Double
    Dim centerX As Double ‘the starting point x points from upper left
    Dim centerY As Double ‘the starting point y points from upper left
    Dim y As Double
    Dim x As Double
    Dim rad As Double
    Dim i As Integer
    Dim numLines As Integer

    numLines = ActiveSheet.Shapes.Count ‘count number of shapes
    For i = numLines To 1 Step -1
    If ActiveSheet.Shapes(i).Type = 9 Then
    ActiveSheet.Shapes(i).Delete ‘get rid of them
    End If
    Next i

    centerX = Range(&quotB1&quot).Value ‘I just used empty cells
    centerY = Range(&quotB3&quot).Value
    length = Range(&quotB5&quot).Value ‘the length of arrow line in points
    ang = Range(&quotB4&quot).Value – 180 ‘so that the arrow will point toward the
    ‘wind direction
    rad = ang * Application.WorksheetFunction.Pi / 180
    x = Cos(rad) * length
    y = Sin(rad) * length
    With ActiveSheet.Shapes.AddLine(centerX, centerY, centerX + x, centerY – y).Line
    .EndArrowheadWidth = msoArrowheadWidthMedium
    .EndArrowheadStyle = msoArrowheadTriangle
    .EndArrowheadLength = msoArrowheadLengthMedium
    End With
    End Sub

    My first time posting code. I hope I did it right.

    This is only for one wind direction of course.

  6. Hi Rob,
    I am creating a chart using ChartSpaceClass.
    oChartSpace.Charts[0].SeriesCollection.Add(0);
    oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimCategories,Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral),names );
    oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues,Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral),totals );
    ‘names’ and ‘totals’ are twa tab limited strings.
    I want this chart to be showed in an excel and save in the server.
    I use SpreadsheetClass to generate excel sheet. How can I link this chart to my Spreadsheet, if I am not using the data from the spread sheet to create Chart.

    If I use,
    oChartSpace.ExportPicture( TargetGIF + strName, “gif”, 800, 600);
    to save the chart as a picture, how can I irsert this GIF to my spreadsheet? Please help me.

    Ajith

  7. I’ve built an app which is a vector map, essentially a scatter chart with a regular array of points, where each point used a custom line segment as its marker, with an arrowhead at one end, and the length and direction of the arrow were determined by values in a table.

  8. Ajith –

    Use Insert menu > Picture > From File to insert the GIF. If you need to automate it, turn on the macro recorder prior to inserting the picture, then edit the code as necessary.

    – Jon

  9. Jon,
    Thanks for the reply. But I am using OWC copmponent to create the spread sheet and the chart. I want to combine both. Or I can insert the GIF file created from the chart into the spread sheet at runtime. Do you have any idea to do this.
    Thanks
    Ajith

  10. Hi Rob,

    I was pleasantly surprised by all ideas on in-cell charts. This is fun.

    You’ll be far beyond this topic (2005) but still …
    Shouldn’t the formula in B7 read =SIN(RADIANS(B4)) and in B9 =COS(RADIANS(B4)).
    In that way 0 degrees point top, 90 degrees to right etc. I wonder if I have a point here.

    If you are interested: see my site, page Example Files, Excel, DiversenGrafieken.xls. On the last sheet, you’ll find ‘changed’ versions of your great examples.
    I also had a fight with the macro code of in-cell line charts. It assigns names to the lines, based on a cell address. And I tried to add more comments (in Dutch). Also funny: I found the WorksheetFunction.Max before reading all comments.

    Kind regards, Frans

  11. Hi Frans.

    Indeed you’ve found a mistake. You’ll note this was a comment earlier on.
    Nice site. You’ve got a lot of material there!

    Cheers

  12. I am creating a chart using ChartSpaceClass.
    oChartSpace.Charts[0].SeriesCollection.Add(0);
    oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimCategories,Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral),names );
    oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues,Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral),totals );
    ‘names’ and ‘totals’ are twa tab limited strings.
    I want this chart to be showed in an excel and save in the server.
    I use SpreadsheetClass to generate excel sheet. How can I link this chart to my Spreadsheet, if I am not using the data from the spread sheet to create Chart.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.