Dr. Mark Gardener 


Statistics for Ecologists Using R and Excel. Edition 2 is on the way. 
Writer's BlocOn this page you can find out about my latest writing project. I'll post updates on progress, tables of contents and also some of the R scripts (and possibly Excel spreadsheets) I am developing in support of the new book. I'll try to keep the material reasonably up to date. The Writer's Bloc homepage contains a table of contents and an index of the pages that contain custom R commands and R scripts.
I am working on a new edition of my book Statistics for Ecologists Using R and Excel. I am currently revising the chapter(s) on graphics and thought I'd make some notes about Tally plots... 

Statistics for Ecologists Using R and Excel: Original Edition 1 Available now from Get a 20% discount on "Statistics for Ecologists" when you buy direct from the publisher! Enter the voucher code S4E20 in the shopping basket at Pelagic Publishing. 
Visualizing Data distribution:Make a Tally plot using ExcelData distribution is important. You need to know the shape of your data so that you can determine the best:
Some statistical tests use the properties of the normal (Gaussian) distribution, whilst others use data ranks. So, it's important to know if your data are normally distributed or otherwise. The classic way to look at the shape of your data is with a histogram, showing the frequency of observations that fall into certain size classes (called bins). However, you can make a "quick and dirty" histogram using pencil and paper, a tally plot. The tally plot is useful as it is something you can do in a notebook in the field as you collect data. 

A classic histogram has a continuous xaxis. Use the hist() command in R to make a classic histogram. 
The classic histogramA classic histogram uses an xaxis that is a continuous variable, like the following example drawn using R:
The xaxis is split according to the bin boundaries and the bars show the frequency of observations that fall between two bin boundaries. This classic histogram is easy to draw using R with the hist() command. The command works out the axis breakpoints and calculates the frequencies for you. 

Excel uses a column chart in lieu of a histogram. The xaxis is therefore discontinuous and split into categories representing the bins. 
Histograms in ExcelExcel cannot draw a "proper" histogram; the nearest it can get is a column chart with separate categories representing the bin classes:
This is not so terrible, but it is not quite the same as a true histogram. In the preceding example the bars have been widened to reduce the gap width, which makes the chart appear more like a real histogram. 

Calculate frequency of observation in each bin class using Excel's FREQUENCY function. FREQUENCY is an array function and places a result in several cells at once. 
Using Excel to calculate data frequencyYou can draw the histogram (column chart) in Excel easily enough but need to compute the frequency of observations for each bin class. To do this you need the FREQUENCY function.
To use the function follow these steps:
Now you have the bins and the frequency so you can build your histogram (column chart), using the Insert > Column Chart button. 

The Analysis ToolPak can calculate frequencies and optionally draw a column chart (histogram). 
Frequency calculation via Analysis ToolPakYou could skip the frequency calculation stage altogether and use the Analysis ToolPak (Insert > Data Analysis), which will also draw a column chart (histogram) for you if you like. If you cannot see the Data > Data Analysis button you may not have the ToolPak installed. Go to the Excel options and the AddIns section, where you can enable it. The Histogram section of the Analysis ToolPak requires you to have some data and a range of bins. So:
Once you've clicked OK you should see the results, the bins will be repeated alongside the appropriate frequencies. If you selected to have a chart output then you'll see a column chart too. If you do not have Windows or indeed Excel, then you can't use the Analysis ToolPak but you can still use the FREQUENCY function. Both FREQUENCY and REPT are part of the armoury of OpenOffice and LibreOffice (and probably others). 

Use REPT to copy text (a tally mark) a number of times (frequency) to build a tally plot in Excel. 
Making a Tally plotYou may not want a graphic and require only a simple tally plot. It is easy to do this in Excel using the FREQUENCY and REPT functions. You use the FREQUENCY function as described before to determine the frequency of observation in various bins. Once you have the frequencies you use the REPT function to repeat some text a number of times (corresponding to the frequency).
In the example I have set the character text I want to use in cell D6. The frequencies are in E2:E12. The REPT function takes the text I want to display as a tally mark and repeats it by the number on the frequency column. Note the cell reference D$6, which "fixes" the row so that the formula stays correct as it it copied down (from G2 to G12). You could use the text directly in the REPT formula e.g. =REPT("X", E2) but then if you wanted to alter the tally plot you'd need to alter all the cells. If you point to a single cell you can alter the tally plot simply by typing a new tally character into that one cell. The tally plot doesn't really replace the histogram but it can be useful to have a plain text representation of your data rather than a graphic. You can copy the spreadsheet cells into various programs.
If it not too hard get the tally plot oriented with the tallies vertical. You'll need to format the tally cells so that they are oriented at 90˚ but otherwise this is straightforward. However, in the "vertical" orientation the cells do not copy/paste so nicely! 

Top 


Providing training for:

My Publications See my personal pages at GardenersOwn 

Follow me... 

Top  Contact  DataAnalytics Homepage 