Probability and Statistics in Microsoft Excel™
Excel provides more than 100 functions relating to probability and statistics. It also has a facility for constructing a wide range of charts and graphs for displaying data. This leaflet provides a quick reference guide to assist you in harnessing Excel’s statistical capability. Except where indicated, the features included here are available in Excel Versions 4.0 and above. Almost all the instructions here also apply to the spreadsheet facility in OpenOffice ( http://openoffice.org-suite.com/ ); any slight variations in commands should be obvious to the user.
Excel is not designed for statistical computing. If you require statistical analysis beyond data validation and manipulation, tabulation, presentation and calculation of summary statistics, you are advised to use a bespoke statistical package such as Minitab or SPSS.
Excel has an Analysis Toolpak optional “add-in” facility that includes macros for carrying out many elementary statistical analyses. The instructions for installation of this add-in vary with the version of Excel - use the Help facility in Excel for further information on this. This add-in facility is not used in this leaflet.
There are two reasons why this add-in should be used with care:
- Unlike other spreadsheet functionality, which ensures that calculations automatically update in the light of changes elsewhere in the workbook, the output from the add-in is not dynamically linked to the source data. Hence if any of the data change the add-in must be run again to obtain updated output.
- Output from the add-in may be misleading (see, for example, http://support.microsoft.com/kb/829252/ )
There are other commercially available add-ins that make use of Excel’s familiar user interface but supplement its statistical functionality. Examples include:
Analyse-it® http://www.analyse-it.com/ R-Excel http://rcom.univie.ac.at/ Unistat http://www.unistat.com/ XLSTAT http://www.xlstat.com/en/home/ StatTools http://www.palisade.com/stattools/