Statistics

My advice is for you to download the files directly to your hard drive by right-clicking on the link and then selecting "Save Target As..." - otherwise the Excel files may not work fully in your browser window!

Available Files:

• Statistics Calculator (Updated 08/02/04)
  I wrote this Excel file as a quick aid to calculating some of the more straightforward Descriptive Statistics from either one or two data sets. Nothing fancy but it is useful in class or for pupils with homework assignments or projects. Amount of data rows limited only by size of spreadsheet! An Automatic Print Button has been added to eliminate the need to use "File -> Print -> Print Range -> Page(s)...." which would otherwise need to be set in order to print only the data rather than any empty data rows in the data table. Excel has a habit of printing EVERYTHING!
  
  

• Interferential Statistics (added 22/04/03)
  I wrote this Excel file for a colleague who teaches Advanced Level Psychology at School. It contains several Significance Tests for which the pupil has only to type in their original data (into the yellow cells) - in fact all other cells have been protected against alteration as many cells contain complex worksheet formulae that must not be altered. Notice that the pupils still have to consider the value(s) that the spreadsheet produces for them from their own Statistics Tables so a little intepretation is still required - I wouldn't want to do it all for them!
  
  Right-Click here to download the Excel file (891KB). I would recommend a HIGH Screen Resolution for this file in order to see the whole of the data columns, etc, if possible.
  
  NON-PARAMETRIC Tests include:-
  
  Chi-Squared Test (a fixed 2 X 2 on one worksheet and variable sized contingency table up to 10 X 10 on a second worksheet)
  
  
  
  
  Wilcoxon Signed Ranks Test
  
  
  
  Mann-Whitney (U) Test
  
  
  
  PARAMETRIC Tests include:-
  
  t-Test for Related Data
  
  
  
  t-Test for Unrelated Data
  
  
  
  
  CORRELATION Tests include:-
  
  Spearman's Rho
  
  
  
  Pearson's Product-Moment Correlation Coefficient (one using standard deviations, etc, and a second using the z-Scores - each on a separate worksheet
  
  
  
  
  
• Interactive Time Series: (41KB)
  This Excel 2000 file calculates a moving average (The period of which can be dynamically changed) from a set of data that can be extended or reduced if required.
  
  
  
• Random Number Generator (35KB)
  This Excel 2000 file gives the user two ways in which to calculate a column list of random numbers.
  Method 1: Here the user chooses an upper value, lower value and the total number of random numbers required in the chosen range - repetitions of numbers are allowed.
  
  
  Method 2: Here the user chooses an upper value and a lower value but now the program returns only these numbers in the chosen range once - that is without repetition.
  
  
  This file is particularly useful in so far as it returns the VALUES only of the Random numbers and not cell values based on the RAND or RANDBETWEEN Functions which would then change their values each time the worksheet was recalculated.
  
  
  
• Edexcel Statistics Coursework (Mayfield High School data set) (Two Sheet Version, Single Sheet Version) (750KB)
  If you have attempted to work with this data set as supplied by the Edexcel Examining Board then you will have noticed several things:
  One - its hard work to get anything that passes for an interesting hypothesis and Two - there is an awful lot of data to manipulate! (Original, un-modified data file here)
  
  With the second of these matters being the one that is easier to control I set to work on creating some dialogue boxes that made sorting on any column easier and for copying any number of columns of interest into a second Excel workbook.
  
  Please note that although Edexcel have seen this file they have not in anyway endorsed it.
  
  
  Notice the buttons to the top left of the worksheet and the AutoFilters on each data column.
  
  
  
  
  An AutoFilter in action.
  
  
  
  
  Selecting columns of interest to be automatically copied to a new Workbook.
  
  
  
  
  The contents of a new Excel Workbook as the outcome of the above automatic column copying. Note also that a non-repeating random number has been assigned to each row of data in this new file. This is very useful if a random sample needs to be picked from your "reduced" data set.
  
  
  
  
  Here we see the original KS3 Worksheet about to be automatically sorted by Year Group. No longer do you need to highlight the columns and go into "Data -> Sort...", etc.
  
  
  
  
  This is the outcome of the above sort which has then had the same columns of interest chosen as above and then this has been automatically copied to another, new Workbook.
  
  
  
  
  Once in the new Workbook you may, if you wish, add your own AutoFilters by first selecting the row inbetween the data column tiltles and the actual data, and then selecting the options in the Toolbar Menu as shown in the picture below.
  
  
  
  
  This is the result of selecting AutoFilter.
  
  
  
  
  These AutoFilters can now be used to manipulate the copied over columns in your reduced dataset.
  Notice that these individual files of reduced datasets, according to what data columns are of interest to either yourself or your pupils, are very much smaller in size that the original data and are therefore much more convenient to store on Floppy Disc, etc - typically 20-40KB.
  
  
  
  
• Books
• Links