Live by the spreadsheet, die by the spreadsheet

There’s a simple relationship that we use quite a bit in analytical chemistry: the relationship between how concentrated a solution is and how much light it absorbs. It’s called Beer’s Law, and in simplified form, it looks something like this:

[A = kc]

… where A = Absorbance (measured by a spectrometer), c = concentration, and k = a constant***. Even if you’re not familiar with Beer’s Law or the instrument it’s used with, you can see that this is a simple linear relationship. If you know the value of the constant k, then you can measure the absorbance of a solution on the spectrometer, then use the equation to find out how concentrated the solution is.

So how do you get the constant? Just make some solutions where you already know the concentration, and measure the absorbance of each one with a spectrometer. The raw data looks something like this.

Test Tube Concentration (M) Absorbance
1 0.0100 0.25
2 0.0150 0.38
3 0.0250 0.63
4 0.0450 1.13
5 0.0600 1.50

Now, what would you do to find that constant, k, that relates the absorbance and the concentration? Here’s a hint – you’ll need to plot the data and perform a linear regression analysis to find out the value of k. This might sound hard, but a modern spreadsheet can make a nice looking plot and perform the linear regression. All you have to do is enter the data, tell the spreadsheet what things to plot, and let the spreadsheet do the grunt work.

Some of my students had to plot absorbance and concentration data as part of a recent laboratory experiment. A depressingly high percentage of these students produced this plot.

[The wrong plot]

Quick! What’s wrong with this plot? (And no fair peeking below to see the answer!)

After making the plot, these students used the value for k that the spreadsheet calculated to find the concentrations of their unknown samples … and failed miserably – reporting concentrations that were several orders of magnitude too high. Impossibly high.

So where did the students go wrong? Their plots should have looked like this.

[The right plot]

Beer’s Law, after all, is a relationship between absorbance and concentration, not between absorbance and the numbers arbitrarily assigned to each test tube for identification! To further the problem, many of these students did not even notice that the concentration numbers they reported were ridiculously wrong.

I’ve been teaching freshman chemistry full-time for more than seven years, and this sort of mistake is much more common now than it was seven years ago.

James Cameron was wrong. The machines will take over eventually, but not via squads of semi-indestructible Schwarzeneggers. They will simply rob us of our ability to think.


*** This constant depends on several things, including the identity of the substance you’re analyzing and the size of the spectrometer’s sample holder.

One Response to “Live by the spreadsheet, die by the spreadsheet”

  1. You make a good point about humans versus machines and who does (or “will do”) the thinking. In the end, though, if people yield to machines in this arena, I believe they will only have themselves to blame.

    Perhaps a better model than Terminators is the character of Satan, as described in Poul Anderson’s “The Broken Sword”, who informs one of his victims, “Mortals never sell me their souls. They give them away.”