Analysis For Average Spreadsheet Joe

Online Cusum Chart with YOUR Data! The CUSUM Calculator Chart

noreply@blogger.com (Maths Fox) — Mon, 11 May 2009 19:24:00 +0000

Copy a single column of numbers (max 90 rows) from Excel and paste in the form below. (Numbers should be separated by a tab- simple copy and paste should work without doing anything fancy.) Click Submit. Then after about 5 minutes refresh this page and hopefully your data as well as the CUSUM should be visible in the chart below.
*(If it doesn't happen in 10 minutes, then there is something wrong; my apologies if this is the case. It could be the format of your data or something going wrong with my code.) But hopefully it will work!

Loading...
This is the output result:

Useful Excel Charting Tip - Useful Script

noreply@blogger.com (Maths Fox) — Fri, 08 May 2009 20:16:00 +0000

So you have lot of data variables in a spreadsheet, and would like to eyeball it in a chart and get a quick idea of relationships between variables etc, but it is too cluttered because of so many series if you tried to put them all one on chart. Wouldn't it be great if there was a quick way to toggle individual series "on" and "off" while looking at your chart?

The usual method is to limit the number of series, delete series or change line properties manually to make them disappear one by one, but thats a lot of work. Besides, to undo the change is just as much work.

Here's a script I put together that lets you toggle series on and off just by selecting and then double clicking the series legend text!

It works by toggling each line series linestyle property between a solid line and no-line, giving the illusion of switching off the line series. It won't remove markers but you could easily adapt it to do that. One limitation- it only works on a chart thats on its own sheet (i.e. not embedded inside a worksheet.)

Instructions:

1. Create your Line or XY chart as you desire, but located as its own sheet. (NB not embedded in another worksheet). Make sure you have a legend (i.e. list of series names displayed), and this works best with line only chart types, although you could adapt it for markers as well.
2. On the menu, Click Tools -> Macro -> Visual Basic Editor
3. When visual basic edit loads, explore and find VBAproject ("Yourfile name")
4. Expand the Microsoft Excel Objects items and find the name of your chart (usually "Chart1")
5. Doubleclick on the name of your chart- it will put the cursor in the code window.
6. Copy and paste the following code into the code window:

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)

If ElementID = xlLegendEntry Then
If SeriesCollection(Arg1).Border.LineStyle = xlLineStyleNone Then
SeriesCollection(Arg1).Border.LineStyle = xlSolid
Else
SeriesCollection(Arg1).Border.LineStyle = xlLineStyleNone
End If
End If
Cancel = True
End Sub

7. Go to the menu File -> Close and Return to Excel
8. Voila... it should work. To test click on one of the legend texts in the legend box, then double click individual legends to toggle them off and on.
9. Remember to save.
10. Repeat for any additional charts you wish to make.

New to Excel VBA? This might help:

Free Online Stats Book (or order the print version thru Amazon)

noreply@blogger.com (Maths Fox) — Sat, 25 Oct 2008 13:09:00 +0000

StatSoft make the very powerful Statistica stats software package. They also make a free online stats book available here. This is quite a useful reference to understanding statistics and advanced analysis methods.

Alternatively you can order the print version, which Amazon retails for around $80.

Quick Tip: Zooming in and out of Excel / Openoffice

noreply@blogger.com (Maths Fox) — Sat, 31 Mar 2007 18:39:00 +0000

If you have a mouse with a scroll wheel, the chances are good that you can take advantage of this tip if you don't already know:

Hold down the CONTROL button whilst rotating the scroll wheel on your mouse -

and you should find that the view zooms in or out of your sheet or graph, centered on whatever the moise is pointing at.

Adding a "Slope" Calibration Mask to your CUSUM chart

noreply@blogger.com (Maths Fox) — Mon, 26 Mar 2007 20:09:00 +0000

For the aid of the viewers of your CUSUM (or your own reference) it is useful to add a calibration scale to the CUSUM. Remember that with a CUSUM its the slope of the trend that conveys the average, and changes in slope that indicate increases or decreases in the underlying data.

The simplest way to add the scale is to add a few series (typically 3) representing reference values for a short period at the beginning of the graph. It might look something like this picture.

To generate values for the series, simple use the same CUSUM method on the calibration value, using the same mean that used previous to generate the CUSUM.

For example, the CUSUM slope for the value 5 might start like this in column G:

[Cell G4] = G3+5-$B$3
[Cell G5] = G4+5-$B$3
where $B$3 is the mean you calculated previously.

Here's the example spreadsheet.

Happy charting!

Quick Tip: Adding Vertical (or Horizontal) lines to an Excel chart

noreply@blogger.com (Maths Fox) — Mon, 19 Mar 2007 18:56:00 +0000

When using CUSUM's or other charts it may be useful to highlight a period or point through the use of vertical lines. You could draw it on manually, but what if you need to change the graph or the value? With an XY chart it is fairly simple to add a vertical line. Lets say you need to add a vertical dotted line at x=7 and your y-axis scale extends from 0 to 10. Here's how you do it:

1. Add a news series to your graph (right click, "source data", series tab, add)
2. For the x-values enter this: ={7,7}
3. For the y-values enter this: ={0,10}
4. Right click one of the new points eg. x=7, y=10 and format data series
5. Click "auto" for Pattern > line and "none" for Pattern > Marker
6. Change line style to dotted (if you prefer, or keep for solid).
7. Voila!

If you need to make the values variable then just enter the cell reference to pick up:
eg. instead of ={7,7} you would like to use the value in cell B2
You guessed it, just enter ={B2,B2} for the x-values.

Interpreting CUSUM graphs

noreply@blogger.com (Maths Fox) — Sun, 18 Mar 2007 13:18:00 +0000

To interpret the CUSUM graph one needs to look at the slope of the graph, and specifically where slope changes occur. A constant slope is an indication of a stable value in the underlying data despite the presence of noise. In the example given earlier, a number of relatively "constant" slope areas can be identified, and these are shown superimposed on the graph. Points at which the slope changes are the turning points and these have been denoted with vertical lines.

So what do you do with the turning points? We'll this gives you an indication of where to average values from. In the example given, the first identified period is from t=1..14s, and the average for this period is 1.0. For the second period t=15..30s, the average is 6.4, and so on. I haven't shown this but you you could add this graphically to the bottom series as straight lines between the turning points for clarity, at the appropriate y-axis average value.

There is some danger in identifying too many turning points, as you could start reading something into the data which just isn't there. The greater the change in slope, the more convincing the turning point. In this example the turning points near 48, 72 and 84 are the most convincing.

To assist in calculating the average from the graph, one can add a calibration scale/mask which shows the relationship between set slopes and average values. We'll save details on how to do that for a later post though.

Applying the CUSUM to your spreadsheet data

noreply@blogger.com (Maths Fox) — Sat, 17 Mar 2007 08:38:00 +0000

The CUSUM is, simply put the cumulative sum of the differences of your data from its mean value.

i.e. CUSUM(t) = CUSUM(t-1) + data(t)-(mean value)

in spreadsheet for if your data is in range from A2..A32, the cusum could be implemented as follows in cells B2..B32: (Make sure cell B1 is empty equal to zero (0).)

B2=B1+A2-average($a$2..$a$32)

Then copy down to cells B3..B32.

Alternatively you could work out the average in a seperate cell and then absolute reference it.

eg. A1=average($a$2..$a$32)

then B2 would simplify to:

B2=B1+A2-$A$1

In the next post we'll cover the interpretation of the CUSUM.

In the graph shown an example of some noisy data (bottom series) and its corresponding CUSUM is presented. Click here to see the data spreadsheet.

Introducing the CUSUM chart

noreply@blogger.com (Maths Fox) — Fri, 16 Mar 2007 22:18:00 +0000

The Cumulative Sum chart (CUSUM) is a very useful (and powerful) tool to pick up small changes in trends of noisy data- and it works just as well if there are large trend changes. Despite what some might say, it is actually relatively easy to apply this technique in a spreadsheet to analyse your data, although one must understand how to interpret the graph.

The CUSUM is very useful to identify important dates or times (turning points) where a trend change occurred, especially where one finds that this is not obvious when looking at the time series data.
You can use it to guage the likelihood that other noisy variables could be causes if one can pick up similar trend changes in these variables. It finds applications in data analysis especially of process data and is a tool that can be used by Six Sigma practitioners.

In my next post I will elaborate on how to implement this useful technique in a spreadsheet such as Excel or OpenOffice, and also show an example, and more importantly how to interpret the graph, so stay tuned. For now, here are some related links.

Cusum Link 1 (NIST)
Cusum Link 2 (iSixSigma)

Quick Tip: Absolute references fast with F4

noreply@blogger.com (Maths Fox) — Fri, 09 Mar 2007 19:57:00 +0000

This might be old hat to some, but its pretty useful if you don't know. Change from relative references (like "=A1") to absolute (like $A$1, $a1, a$1) in formulas just by clicking on the reference whilst editing and pressing F4 (excel) or Shift F4 (openoffice).

Graphing (more) like a Pro

noreply@blogger.com (Maths Fox) — Thu, 08 Mar 2007 19:14:00 +0000

Ever noticed the differences between a stock-standard excel XY graph and graphs you see in papers published in Journals? They tend to look cleaner and professional whilst the standard ones in excel (minus tweaking) look like a high school project?

<<>>

So here are some tips to get a more professional look:

1. Get rid of the gray background (Right click & Format Plot Area)

2. Get rid of gridlines, unless they really do add value to your data (Right click & Chart Options)

Quite often a single dotted line to show a threshold value will suffice and at the same time save clutter on your graph.

3. Choose appropriate scaling and keep the axes out of the graph area by choosing where the x and y axes cross each other (Right click the axes, format, scale)

4. Put ticks whether major or minor on the inside. (right click, format, patterns)

5. Keep axes labels short and sweet and give the units in brackets, eg. time [seconds]

6. Place the legend in the graph, this gives a bigger graph view in the same space and its easier for your readership to see where the legend is.

7. Use markers and lines sizing carefully to make the data clear. You often don't need both markers and lines.

8. Don't put too many series on one graph unless it really is necessary.

Exponential Filter/Smoothing

noreply@blogger.com (Maths Fox) — Mon, 05 Mar 2007 19:48:00 +0000

#1
The problem...
The problem is you've got noisy time series data. Ideally you would like to plot it on graph to show the average trend or compare it with another variable. BUT its too noisy and clutters the graph if you plot it with markers and lines. With just markers it appears as a scatter cloud?

Aha, then you see that Excel has the option to add a "trendline". But you can't seem to find one that does the data justice -or- its just plain wrong in places. The "moving average" seems the better one of the options, but to get good smoothing you have to average many periods. That has some bad effects: it delays when changes occurs, especially when comparing to another variable (plotted in another series).

Resist the temptation of moving average!!!

A solution ...
A nice easy way to sort out this data is to apply a simple exponential filter or smoothing. There are better, but this is simple and for 80% of the time will work great.

How does it work?

I will illustrate this in the spreadsheet, but the maths is simply like this:

New Smoothed value = (1-factor) * actual data + (factor)*(previous smoothed value)
or:
y_n=(1-k)*x_n + k*y_n-1
y = smoothed value
x = actual (noisy) data
k = smoothing factor, a real number between 0 and 1. 0= no smoothing

How to set this up in a spreadsheet:

1. Estimate a value for the starting smoothed average. I usually use the first raw actual data point or take an average of the first few points.
2. Setup the filter to the right of the actual data points.
3. Reference the smoothing constant "k" as cell using absolute $ reference eg. $b$1. This will allow you to adjust it easily.
4. Copy the filter down so that you have two columns- to the left your original data and to your right the smoothed values.
5. Play and adjust "k" to suit your needs. As a first guess use 0.5. I commonly use anywhere from 0.5 to 0.85. Be wary of values above this as you could be over smoothing, but it does depend on your application.
6. For graphing purposes I suggest keeping your original raw data series (but use markers only, i.e. scatter plot) and then plotting the smoothed trend as a second series (lines only).

http://www.4shared.com/file/11752632/8565b041/exponential_filter.html

What's the purpose of this site?

noreply@blogger.com (Maths Fox) — Mon, 05 Mar 2007 19:39:00 +0000

In both my work and hobbies I've needed to make use of spreadsheet analysis of data, and I've learnt a few things on the way. I've put up this site to share what I've learnt, and hopefully may even learn things in return from fellow posters. I don't claim to be an expert (I do hold an engineering degree though) and although you can get advanced books on the subject they are often not in a format that can be easily and quickly applied to one's problem at hand.

So I really hope the methods, hints and examples given on this site will help you in work, hobbies or other interests!