Error calculations (error of slope, intercept)
This is a set of instructions modified from a linked website by Jonpaul Wright: FINDING SLOPE, INTERCEPT, AND ERROR IN EXCEL FOR MAC
FINDING SLOPE, INTERCEPT, AND ERROR IN EXCEL FOR MAC
Excel for Windows has a built-in Data Analysis toolkit to calculate error in a number of statistics. For Macs, errors have to be calculated differently using a function called “LINEST” for “linear estimate”. This is a special type of function called an ‘array’ function. Below are instructions on how to use LINEST on a Mac to find the slope, intercept, error (standard deviation of the mean) in both.
1. On your Excel spreadsheet set up a 5 x 2 table with the following labels:
2. Once you have this setup, select the 5 x 2 array (the 5 rows and 2 columns between your labels).
3. Now go to functions (f(x)) and select the function LINEST.
4. A table will appear and ask you to select the rows containing your data. The first line asks for the y values, and the second line asks for the x values. Type in True for the next 2 questions. Your entry should look like this:
You should see this at the top of your spreadsheet (shown below):
=LINEST (F17:F21,E17:E21, true,true)
Now move your cursor so it is positioned after the final parenthesis and hold down the command key as you hit enter.
Values should now appear in your previously highlighted 5 x 2 array.
Calculating confidence intervals
The above calculation generates (±) statistics for both the slope (m) and the intercept (b). This is a standard deviation value and is generally considered accurate with datasets of 20 or more values. For smaller datasets, one can use a confidence interval (usually 95%).
This can be calculated by using t value tables (Google, textbooks, Chem 6 lab manual) and the standard deviation. Simply multiply the standard deviation value by the corresponding t-value for N-2 degrees of freedom (DF).