Favorite functions: Simple statistics

There’s a lot of pretty amazing things that a trained statistician can tell about a set of numbers, given the right statistical tools. I, however, am not a trained statistician, and neither are most people.

For most people, there’s really only three things that they want to know about most things: how many, how much, and the average. Fortunately, in addition to a dizzying array of tools to perform higher level statistics, every spreadsheet will provide basic tools to answer these three questions.

How many is addressed by a set of tools including count, countif, countblank, and several others. My personal favorite, due to the sorts of things I use it for, is countif(range,condition). It counts the times that values meeting the condition appear in the range.

How much can usually be addressed by sum(range). All it does is add up all the numbers in a range. There are variations on this including sumif (add up all the numbers that meet a certain criteria), sumprod (figure out the product of the corresponding numbers in 2 or more ranges, then take the sum of the results), and sumsq (figure out the square of each number in the range, and then take the sum of the results). However, sum(range) can cover all of these if you’re willing to do the other steps yourself.

Finally, the average can be determined by a rather intuitive function: average(range). This doesn’t really have any variants worth mentioning here, but it is worth noting that if you’re also supposed to find the median and mode of a set of numbers, there’s functions for those, too. They’re median(range) and mode(range) respectively. You don’t even need to put the numbers in order!

Next week, in honor of a very special day (for me at least) I’m going to explain how you can use these tools to generate a very specific weighted average that’s near and dear to the hearts of students everywhere: the GPA.