Simple statistics: counting numbers

If you want to know how many numbers are in a given group of cells, there’s an easy way to do it: use the count() function.

Imagine, for a moment, that you have a list of 100 dates. The question is — are they all numbers? It matters because if they’re numbers, you can use a variety of functions to extract the year, month, and day. If they’re not numbers, then to extract the year, month, and day you have to use text-manipulation functions such as right(), left(), and middle().

So you can take your group of dates, and use count() on them. If the dates were in a1 through a100, the formula would be =count(a1:a100). If the result is one, they’re all numbers. If the result is zero, they’re all text. If it’s somewhere in between you’ve got a mixed of numbers and text, which you may be able to separate by sorting it. It may ask you if you want to sort text that looks like numbers as if it were numbers, and if you say no then the two formats will end up separated.

Count() will also let you check more than one range at a time. You could count all the numbers in column A and all the numbers in column B in the same function. It’s worth noting, however, that if two ranges overlap, then you’ll be counting any numbers in the overlapping section twice. For example, if you counted all the numbers in column A, and all the numbers in row 1, then if cell A1 had a number in it then your result would have one more number than is actually there.

All that said, let’s take a look at what does and does not count as a number. In the tables below I’m going to give you a variety of things, showing both the actual contents of the cell (the formula), and what you see if you’re not inside the cell, showing the formula (the result).

Numbers
Contents	Appearance	Notes
=1	1
=5/2	2.5
=7+9	16
=today()	5/27/12	This returns today’s date, as a number.
=pi()	3.14159	Remember geometry? area=π r2? pi() returns π
8717044007104620	8.717E+15	This is scientific notation — very helpful when you’ve got a whole lot of digits and only care about the most significant ones. It’s essentially saying 8.171 * 10^15.

Non-Numbers
Contents	Appearance	Notes
’3	3	Note the ‘ instead of the =. That makes it a character, even if it’s a character that normally represents a number.
‘Three	Three	The spelled-out names of numbers are not numbers.
1 2 3 4 5 6		Pretty much, if it has characters that are neither numbers, part of a function name, or mathematical operators, it’s not a number. This even counts spaces.
=left(“05/27/12″,2)	05	“05″, since it was extracted from text, using a text manipulation function, is still text.

Simple statistics: counting rows and columns

Sometimes, when working with spreadsheets, you need to know how many rows or columns are in a table, without particularly needing to know anything about their contents.

Simple statistics: Counting

One thing that people often want to know, especially if they’re storing lists of things in spreadsheets, is “how many” there are of something.

Fortunately, there are a fair number of ways to get a spreadsheet to tell you how many it has of something. Unfortunately this means it can be pretty confusing when trying to pick the right one for your needs. So, here’s a quick guide as to what functions are commonly available, and what they do. In the next few weeks I’ll talk about them in more detail, including how they overlap and how to emulate one with another. Then I’ll give some examples of how you might use them in the workplace or in daily life.

Example: GPA as a weighted average

As promised last week, today I’m going to talk about how to use a spreadsheet to calculate weighted averages in general, and Grade Point Averages (GPA) in particular.*

A weighted average is just any average where some values are more important. Values with greater importance are said to be weighted more heavily. A GPA is the perfect example of that.

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.

Formatting elements: Alignment (Nifty tricks)

First off, in honor of spring I just want to say that it’s time to…

Making geometric figures (a nifty trick with text orientation and cell merging)

Last week I promised that I’d discuss how to use text orientation in spreadsheets to make regular geometric figures.

To start off with, a regular geometric figure (also known as a regular polygon) is any figure where all the sides are the same length, and all the angles are the same size. A square is a regular polygon, but a rectangle isn’t because the sides aren’t all the same.