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

Basic statistical calculations

basic

Trend line functions

trendConfidence 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. conf_tconf_norm

Distribution Tables

You can also access the t-distribution tables:

t_dist_2t

t_inv_2t

And the f-distribution tables:

f_inv

f_dist

Tests of Probability

z-testf-testt-testchisq

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.

data_analysis

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s