Book contents
- Frontmatter
- Contents
- Preface to the second edition
- Preface to the first edition
- Chapter 1 Introduction to scientific data analysis
- Chapter 2 Excel and data analysis
- Chapter 3 Data distributions I
- Chapter 4 Data distributions II
- Chapter 5 Measurement, error and uncertainty
- Chapter 6 Least squares I
- Chapter 7 Least squares II
- Chapter 8 Non-linear least squares
- Chapter 9 Tests of significance
- Chapter 10 Data Analysis tools in Excel and the Analysis ToolPak
- Appendix 1 Statistical tables
- Appendix 2 Propagation of uncertainties
- Appendix 3 Least squares and the principle of maximum likelihood
- Appendix 4 Standard uncertainties in mean, intercept and slope
- Appendix 5 Introduction to matrices for least squares analysis
- Appendix 6 Useful formulae
- Answers to exercises and end of chapter problems
- References
- Index
Chapter 10 - Data Analysis tools in Excel and the Analysis ToolPak
Published online by Cambridge University Press: 05 March 2012
- Frontmatter
- Contents
- Preface to the second edition
- Preface to the first edition
- Chapter 1 Introduction to scientific data analysis
- Chapter 2 Excel and data analysis
- Chapter 3 Data distributions I
- Chapter 4 Data distributions II
- Chapter 5 Measurement, error and uncertainty
- Chapter 6 Least squares I
- Chapter 7 Least squares II
- Chapter 8 Non-linear least squares
- Chapter 9 Tests of significance
- Chapter 10 Data Analysis tools in Excel and the Analysis ToolPak
- Appendix 1 Statistical tables
- Appendix 2 Propagation of uncertainties
- Appendix 3 Least squares and the principle of maximum likelihood
- Appendix 4 Standard uncertainties in mean, intercept and slope
- Appendix 5 Introduction to matrices for least squares analysis
- Appendix 6 Useful formulae
- Answers to exercises and end of chapter problems
- References
- Index
Summary
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.
- Type
- Chapter
- Information
- Data Analysis for Physical ScientistsFeaturing Excel®, pp. 428 - 443Publisher: Cambridge University PressPrint publication year: 2012