Worksheet Functions

Excel provides a great many built-in worksheet functions such as LOG, ABS, ROUND, etc. In particular, there are a great many statistical functions such as AVERAGE, COVAR, NORMDIST, etc. A list of the functions that are most useful for our purposes is given in Excel Capabilities.

As mentioned in Excel Spreadsheets, functions can be entered into a cell manually or by clicking on the symbol fx to the left of the formula bar to access Excel’s function building capability. Alternatively these capabilities can be accessed via Formulas > Function Library.

Differences between Excel 2007, 2010 and 2013

Excel 2010 includes all the functions of Excel 2007 plus a number of additional functions as explained in Built-in Excel 2010 Statistical Functions. Excel 2013 also adds a few additional functions (see Built-in Excel 2013 Statistical Functions). In this website we will tend to use those functions that are common to Excel 2007, 2010 and 2013, although we will highlight differences where this is appropriate.

One of the criticisms of Excel 2007 is that in certain extreme cases some of the built-in functions are not sufficiently accurate. This was corrected in Excel 2010. New algorithms were implemented to improve the accuracy of these functions and to eliminate any known errors. Excel 2010/2013 also provide more consistent names for the functions.

Real Statistics supplemental functions

While Excel provides a number of statistical functions, as has been noted previously, it lacks some important capabilities provided by standard statistical packages such as SPSS and SAS. The Real Statistics Resource Pack provides a collection of supplemental statistical functions to augment the built-in capabilities.

To access these supplemental statistics functions you need to first install the file called realstats.xlam as described in Free Download.

6 Responses to Worksheet Functions

  1. John D Abernethy says:

    As a grateful previous user, still using OSX Yosemite version 10.10.5, I am now looking forward to establishing that the 2-parameter Weibull function is indeed a good fit to data on the human mortality distribution as a function of age. First of all this will involve using a known iterative routine to extract the Weibull shape parameter by the principle of maximum log likelihood.

    • Charles says:

      I understand from looking at the Internet that Excel users have had problems when using OSX Yosemite version 10.10.5. Has this now been fixed?

  2. Steve says:

    I am currently using Excel 2008 on a Mac; will that system with the material presented here?

    • Charles says:

      There is a MAC version of the Real Statistics Resource pack, but it does not work with Excel 2008. The reason is that the Real Statistics Resource Pack uses Excel’s VBA, but Excel 2008 doesn’t support VBA.

  3. kelsie says:

    when and why do you use statistical functions in a Worksheet? This is my question my teacher asked me today and I have no clue.

Leave a Reply

Your email address will not be published. Required fields are marked *