Daily Archives: January 20, 2013

Cuddling up to Microsoft Excel

Posted on by
Reply

Microsoft Excel is like my fussy white cat. She’s hard not to love, but if she’s not worshiped properly she may turn against you. Go too fast, expect a hard swipe and bite.

Don't mess with me... but I *am* purr-bel-icious!

Don’t mess with me… but I *am* purr-bel-icious!


She must not be manhandled; one approaches her with appropriate reverence and respect. First, proffer the finger for sniffing; then hold finger still as she rubs her cheek against it. Only after these formalities am I allowed to get cuddly and lovie with kitty.

I love Excel for the power it has to create quick displays, and return nice results for standard deviation, mean, standard error, etc… but its convoluted ways of labeling can be so annoying! You must approach the graphs with special attention and genuflections.

Want to create a line graph? Then … certainly don’t press line graph; go to XY scatter plot and pick the icon with the curvy lines connecting the dots!

Want to create a bar graph? Then… certainly don’t press bar graph; go to column graph instead. Arrrggghhh.

If you have any tips for using Excel to create good data displays for investigations, please feel free to share them in comments below.

Onward to science fair!

Standard Deviation and Standard Error

Posted on by
Reply

Students looking for more advanced analysis on their science fair projects will want to understand standard deviation and standard error.

It’s fine to define measures of central tendency using mean, median, and mode, but what about the spread, or variance, of the data? A box and whisker plot very nicely shows the spread of data in the width of the box (since the box contains exactly half the data), but using standard deviation we can see much more.

Here’s a quick video going over the derivation of standard deviation:

I like to give the students some sense of where these equations come from!

Next, we have standard error. It’s helpful to run through scenario with the students where you imagine “What if we had a very, very large N? How would that affect the Error?” (denominator increasing causes the ratio to decrease – which makes sense, that a large sample size would decrease error). “What if we had a very small standard deviation? The dependent variable returned almost precisely the same value for each trial?” (numerator decreasing also causes the ratio to decrease – again, less error if there’s more consistent correlation between the independent and dependent variables.)

The next video explains how to place error bars on Excel. I don’t know why, but whenever I try to use Excel to place standard error bars according to its default settings, it doesn’t seem to be accurate. You’d think it would be, since there is the option “Error bars – use standard error” – but this option creates the same error for each of the different bars in a bar graph. In most of our science experiments, each bar is going to need its own error measurement because it represents its own set of data…

Someone else on Youtube very nicely explained how to create each bar with its own standard error bar (I’d give him credit here, but I don’t remember the video exactly!), so I used his method to create this explanation here:

Hopefully, advanced 8th grade students who want to be competitive at science fair can apply these ideas to their own data sets.