Excel and statistics for physical scientists

Published 15th February 2017 by Andy Connelly. Last updated 9th May 2017.

Introduction

Excel is anything but ideal for use in statistical work. It can do many amazing things; however, it can be quite opaque as to what is actually happening and difficult to find the correct function. For this reason I have picked out the key functions for the statistical analysis discussed here. You can find more functions here: http://www.excelfunctions.net/Excel-Statistical-Functions.html

I have attached an an Excel file (excel_andyjconnelly_v1) to this post with many of the functions used in a relevant setting.

DISCLAIMER: I am not an expert on statistics. The content of this blog is what I have discovered through my efforts to understand the subject. I have done my best to make the information here in as accurate as possible. If you spot any errors or admissions, or have any comments, please let me know.

Basic functions

Basic statistical calculations

Trend line functions

Confidence Limits

Using Excel you can manually calculate the confidence interval or you can use the function for calculating it directly. However, the descriptions of these functions are very unclear and I’m still not sure how much I trust them – I have edited the descriptions below and they seem to do what I describe. I have attached an Excel file (excel_andyjconnelly_v1) to this post with both a manual confidence interval calculation and the Excel function.

Distribution Tables

You can also access the t-distribution tables:

And the f-distribution tables:

Tests of Probability

Data analysis tools

Another way to carry out statistical tests in Excel there is an Add-in called “Data Analysis Tools” accessible through Options if it is not automatically present. This gives you various outputs, however, the output does not automatically update if you change the values you are analysing.

Further reading

1. Data analysis for chemstry: An introductory guide for students and laboratory scientists, Hibbert & Gooding, 2006
2. http://www.excelfunctions.net/Excel-Statistical-Functions.html

Advertisement