Archive for the ‘Charts’ Category.

Slow plot in Excel

It takes a click or two to create a chart in Excel, but it was not always that easy.

Once upon a time, in the days of Commodore 64, Sinclair ZX81 and Spectrum, Amstrad/Schneider 464/6128, and BBC Micro, charts had to be programmed using PLOT(x,y) or a direct POKE to the screen-mapped memory. Sounds tedious, but it was a definite improvement over the IBM 1130 with a drum plotter which had to be fed punch cards; drum and pen movement were controlled separately in incremental mode. Well, I am the same age as Fortran IV.

The machines were slow, so one could always see the chart being plotted dot by dot. That is what I miss these days—for some charts (scatter, polar) it is useful to slow down plotting so one can actually see the line being plotted.

Here is a small VBA program to animate a scatter chart by dynamically changing (increasing) the chart's data source range—row by row every time increment (tick).

In a VBA module:

Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long
'
Public Sub AnimateChart(SheetName As String, _
                    DataRangeName As String, _
                    Optional ChartIndex As Long = 1, _
                    Optional Tick As Long = 50)
    '
    ' Comments  :  Animates chart by dynamically changing the
    '               data source range
    '
    ' Parameters:  SheetName - name of the sheet the chart is on.
    '              DataRangeName - named range for the data,
    '                              includes header.
    '              ChartIndex - 1 for the first chart on the sheet,
    '                           2 for the second etc..
    '              Tick - update interval;
    '                     suggest: 50, 100, 150
    '
    ' Modified  :  2008-06-29
    '
    On Error GoTo PROC_ERR
    Dim Chrt As Chart
    Dim rngSrc As Range
    Dim rngRef As Range
    Dim NumberOfPoints As Long
    Dim CurrentPoint As Long
    Dim OldPoint As Long
    Dim Strt As Long
    Dim rw As Long
    Set Chrt = Worksheets(SheetName).ChartObjects(ChartIndex).Chart
    Set rngRef = ThisWorkbook.Names(DataRangeName).RefersToRange
    NumberOfPoints = rngRef.Rows.Count - 1
    CurrentPoint = 0 'current animation point
    'clear the chart (show first point only)
    rw = 2
    OldPoint = 1  'first point is in the second row of the range
    Set rngSrc = rngRef.Resize(rw)
    Chrt.SetSourceData Source:=rngSrc, PlotBy:=xlColumns
    DoEvents 'update screen
    Strt = GetTickCount 'remember start tick count
    Do
        CurrentPoint = CLng((GetTickCount - Strt) / Tick)
        If CurrentPoint > OldPoint Then
            rw = rw + 1
            Set rngSrc = rngRef.Resize(rw)
            Chrt.SetSourceData Source:=rngSrc, PlotBy:=xlColumns
            OldPoint = CurrentPoint
        End If
        DoEvents
    Loop While (CurrentPoint <= NumberOfPoints)
PROC_EXIT:
    Set rngSrc = Nothing
    Set rngRef = Nothing
    Set Chrt = Nothing
    Exit Sub
PROC_ERR:
    MsgBox Err.Description
    Resume PROC_EXIT
End Sub

In a Worksheet module, the button's click event—cbtAnimate_Click()—prepares data for a specific chart and calls AnimateChart(). Chart data should be defined as a named range which is passed via the DataRangeName parameter.

Private Sub cbtAnimate_Click()
    cbtAnimate.Caption = "busy..."
    AnimateChart SheetName:=Me.Name, _
                DataRangeName:="Lissajous_Data", _
                ChartIndex:=1, _
                Tick:=50
    cbtAnimate.Caption = "Animate"
End Sub

You can download the workbook with examples for a Lissajous curve, a logarithmic spiral, and a rose curve.

Temperatures

Just practicing. The readings should update hourly, and are hooked to the weather office.

  Temperatures (deg C)  
Toronto Vancouver Yellowknife

Misleading charts

Fancy, but quite misleading graphs. Can you actually see that "A" and "G" are the same? Which one do you think is the largest number? Would you say that "D" is greater than "C"? They call it the funnel chart.

 

 

Ok, now the same data represented by its first cousin, the pyramid chart.

 

 

Can you actually believe this is the same data set? Who came up with this nonsense?

Here are the actual numbers and the plain bar chart.

Label Value
A 600
B 800
C 850
D 875
E 760
F 598
G 600


Something to think about. Moral of the story: beware the fancy graphs, especially when dealing with finances. Apparently the funnel and pyramid charts are frequently used in sales projections and similar. My suggestion, use the bar chart or pie chart instead.

And finally, a good reference on charts; The Visual Display of Quantitative Information by Edward R. Tufte. Among good practices you will also find sections on graphical integrity, distortion in data graphics, lie factor, and the data-ink ratio.

Sigma in time

Not bad for historical data, made by Google Spreadsheet.
The same Hub-Height data as for the previous bubble chart, this time aggregated by day.

 

Notes:

  • Click on the 1 m in the top-left corner of the chart to get the feeling for the zoom feature.

No bubble trouble

If you cannot see the bubble graph from the previous post check the following:

1. Make sure your firewall does not block  http://spreadsheets.google.com.

2. If the firewall is OK, then you may need to:

The Flash Player problem frequently happens on installations where IE6 was upgraded to IE7.
On some sites it would work fine, on some not.

Quality bubbles

Love this graph. Here is some real data from a line for a period of 20 weeks. The date in the bottom-right corner is always Monday and data is for the week of the date.

 

 

Notes:

  • Sigma = standard deviation.
  • Cpk = How many 3-sigma intervals is the mean away from the nearest limit?
  • Cp = How many 6-sigma intervals fit into the specification window?
  • Quality = Number of good parts divided by total parts measured; for the specific measurement.

 

Hey, I can do it too

Still far from Mr. Rosling, but not bad. Click around a bit, it works.

Presenting stats

Presenting statistical data is always a challenge; the stuff is inherently boring, no matter how useful it may be. Here is the right way to do it—watch the master at work.