Introduction

The process of analysing experimental data frequently involves many steps which begin with the tabulation and graphing of data. Numerical analysis of data may require simple but repetitive calculations such as the summing and averaging of values. Spreadsheet programs are designed to perform these tasks, and in previous chapters we considered how Excel’s built in functions such as AVERAGE() and CORREL() can assist data analysis. While the functions in Excel are extremely useful, there is still some effort required to:

• enter data into the functions;

• format numbers returned by the functions so that they are easy to assimilate;

• plot suitable graphs;

• combine functions to perform more advanced analysis.

Excel contains numerous useful data analysis tools designed around the built in functions which will, as examples, fit an equation to data using least squares or compare the means of many samples using analysis of variance. Once installed, these tools can be found via Analysis Group on the Data Ribbon. The dialog box that appears when a tool is selected allows for the easy input of data. Once the tool is selected and applied to data, results are displayed in a Worksheet with explanatory labels and headings. As an added benefit, some tools offer automatic plotting of data as graphs or charts.

In this chapter we consider several of Excel’s advanced data analysis tools which form part of the Analysis ToolPak add-in, paying particular attention to those tools which relate directly to principles and methods described in this book. The Histogram and Descriptive Statistics tools are described in sections 2.8.1 and 2.8.2 respectively and will not be discussed further in this chapter. Tools which relate less closely to the material in this book are described briefly with references given to where more information may be found.