How can I calculate an error for a slope in my lab report using Excel?

[WarPhalange]

I am doing a lab report, but this isn't a homework question per se...

I just wanted to know what the best way of calculating an error for a slope is. I am doing a Continuous Wave NMR lab and took some data with estimated uncertainties, and need to know how to propagate them into an error estimate of the slope the data forms.

Excel just gives me a slope by itself, but no error. The closest thing I could find was regression which was pointless because it didn't take into account my error estimates.

Then there was making your own lines and eye-balling an error estimate from the max and min slopes you can fit through your data. Makes sense, but that's way too wishy washy for me.

Besides that I can say f/B = gamma (what the slope should be), take that for each set of data points, take an average, and propagate that error... but can't Excel just do a damn uncertainty for me? Gah...

 

[Redbelly98]

Do you have the Data Analysis add-in for Excel? (If not, see end of this message.) That will give an error estimate for the slope.

BUT it won't take account of the (differing) uncertainties in your data. A work-around for that is to make multiple entries for each data point, where the number of entries is proportional to 1/uncertainty² for that data point. That is a bit of a pain to implement, but if Excel is your only available tool then that is the way to go.

=====

Adding the Data Analysis add-in

This works in Excel 2003 for Windows/PC.

Select Tools, and look for Data Analysis in the Tools menu.

If Data Analysis does not appear in the Tools menu:
Select Add-Ins from the Tools menu
Check "Analysis ToolPak" and click OK

Data Analysis should now appear in the Tools menu (for me, it's at the very bottom of the menu)

Go to the Tools menu and click on "Data Analysis"
Select "Regression", click OK
Fill out the form menu that pops up. "Output range" can be a single cell, off the the right or down below the data in your worksheet. The regression output will fill in cells to the right and below the "Output Range" cell.

Click OK

In the table that is written into your worksheet, the slope is the number after "X Variable 1", and the error (standard deviation) in the slope is the number to the right of that, in the "Standard Error" column.