Before we begin, let’s review our exercise from the last lesson. Did you think the median was more appropriate for Dataset A and the mean was more appropriate for Dataset B? You are correct! Dataset A has far fewer entries, so the median is a better way to describe the middle. Now back to our regularly scheduled programming…
As we’ve seen in previous lessons, rows and columns keep us organized when looking at student information. Did you also know that you can get your spreadsheet to do virtually any calculation you want? That’s right! All you have to do is ask it nicely.
So what is the magic word? Unfortunately, “calculate sesame” is not the right answer, but we’ll practice calculations throughout this lesson.
Step 1. Double-click a cell and type the equals sign (“=”).
Step 2. Pretend your spreadsheet is a calculator and ask it to do whatever you want. No, really. That’s what you should do right now. Type in a crazy-hard problem, and it will give you the answer instantaneously.
For example, I typed in “=46+323”. Now, some would argue that formula is not “crazy-hard,” but to my spreadsheet’s credit, it immediately gave me the correct answer of “369”. Just so we’re clear, here are the common mathematical operations along with the symbols you need to use within your spreadsheet:
- Multiplication: *
- Division: /
- Addition: +
- Subtraction: –
Don’t forget to start any calculation—called a “formula” in spreadsheet-speak—with your trusty equals sign! Otherwise, you’ll just see what you typed as text and no calculation will take place.
Exercise: Unlocking spreadsheet formulas
Now let’s take a look back at our favorite student, Jimi Hendrix, and his math state assessment data over the last few years. Download this CSV and open it in your spreadsheet. You should see something like the following:
Diving into the pun of yesterday’s lesson title (What does “average” mean?), we recall that these terms “mean” and “average” are often used synonymously. In terms of vocabulary, the mean is the average, and the average is the mean. They are interchangeable, but your spreadsheet prefers the term “average.” See what I mean? (Sorry, bad joke.)
Looking back at the data, it might be helpful for us to know Jimi’s mean math state assessment score over the five-year period. Let’s harness the power of our spreadsheet and make it do the heavy lifting for us!
To automatically get the mean, click an empty cell anywhere on the spreadsheet, type “=AVERAGE(D4:H4)” into it, and hit “Enter”. You should get a mean of 2515.2.
At this point, you might be thinking, “I’m not a nuclear physicist, but I’m pretty sure we can’t average letters.” In which case, you are correct! However, in our spreadsheet, we can reference cells instead of painstakingly typing every, single, number. Each cell has an address, which is a combination of its column letter and row number. This actually makes life much easier. When you type “=AVERAGE” and then an open parenthesis, you can then just drag your mouse across the cells you want to average. When you reference a cell, you can make edits to the data without having to change the numbers within the equation as well.
In addition to the operations above, we can tell the spreadsheet to calculate sum, median, mode, and range by typing “=SUM” with an open parenthesis, “=MEDIAN” and so on.
Finally, there’s a way to count information within a group of cells. For example, in the data table above for Jimi Hendrix, let’s pretend we wanted to find out how many tests he had taken over that time period. Obviously, we could just as easily count in this case, but imagine if there were double or triple digit numbers of test scores. We wouldn’t want to scroll through the entire spreadsheet saying in our heads, “1, 2, 3, 4, …” with our fingers blotting the screen and our eyes squinting!
For the counting utility, type “=COUNTA(D4:H4)”, or enter “=COUNTA” with an open parenthesis and drag across the cells you want counted. Both actions will accomplish the same task. When you hit “ENTER”, you see the number “5”. Jimi took 5 tests over that time period. That seems pretty obvious given this small amount of data, but boy will you be glad you know how to do that when you start to look at larger data sets!
- Who knew a spreadsheet had the power of thousands of calculators?! Tweet
- Drag your mouse across cells to reference them. Typing numbers is t-e-d-i-o-u-s! Tweet
- No need for fingers and toes anymore! Use “=COUNTA” to count your data. Tweet
We’ve just scratched the surface of what you can do with spreadsheet formulas, and there are a ton more built-in formulas than what we covered today. For now, though, isn’t it nice to know that spreadsheets can calculate mean and median with a few keystrokes and little more than the click of a button? Hold onto your hat; tomorrow, we’ll look at data through a new lens.
To get a feel for how a set of data is spread around its mean or average, you could take a look at standard deviation. The larger the standard deviation, the more data is spread out around the mean. A small standard deviation means most of the test scores, for example, were very close to the average. A large standard deviation might mean some people studied very hard while others forgot which test they were taking in the middle of it! Type “=STDEV” and an open parenthesis to select data and utilize this helpful summary statistic.