start

Let’s do some math with Google Spreadsheets. The OG of programming is doing math, and the practice of learning how to do math well, is very similar ot the practice of being a good programmer. Spreadsheets are a handy way to see the connections, and incidentally, if that sort of thing is relevant to you, to learn a tool that lets you do some real fancy math.

Click in any space on the spreadsheet, usually referred to as a “cell.” Just so we are on the same sheet (haha), let’s agree to both click on the top-left cell. Note that when you do this, the A above the cell, and the 1 to its left, both have a slightly darker background color than the other letters and numbers.

In fact, let’s take a minute here to notice these letter and numbers. Any programming you do with Google (or Microsoft) spreadsheets will require you to get very familiar with how these symbols work, and how you are supposed to use them.

Try scrolling as far down and to the right you can. You’ll see that you can get to Z on the right side and to 1,000 vertically. So is that it – do you have 26,000 cells to work with? Not really – you can add more rows and more columns if you want. We don’t yet know how to do that, at least if you haven’t worked with Sheets before, but if you believe me that this is possible, that you can add more rows or columns, it’s logical to ask, Well, how many more cells can I add?

Nothing in the product itself immediately answers that question. But you can search on the Internet and find out – what search query will find the right information is left as an exercise for you, but I’ll tell you the answer – at the time I am writing this, Google says you can have no more than 256 columns, or 400,000 cells, whichever limit is reached earlier. (Another exercise is to figure out from these numbers what the maximum number of rows is, that you can add.)

Well, we know something about the limits of these symbols – how “big” they can get. But let’s step back a bit and ask – why are they there at all? Answering that question is really going to be our first foray, I think, into programming proper. But it’ll take us a few steps first to prepare ourselves.

Let’s go back to just doing math – type in a number into the top-left cell, then click to the cell one to the right (which highlights B and 1, respectively, in the column and row symbols) and type in another number. Let’s say you typed in 23, and 52. Now pick a cell to the bottom of these (let’s say you pick cell A2) and click the following menu items: Insert, Function and SUM.

Now, use your mouse and carefully, starting at A1, click down and then drag it across to cell B1. If you didn’t get the sum of 23 and 52, because your mouse was being unreliable, or your cat jumped up onto the keyboard while you were executing this maneuver, don’t worry. Just start again from the beginning – Sheets can be very forgiving!

If you get the sequence of actions right, you’ll see 75 in the cell A2. Now click in this cell (A2) and notice what it says in the “formula bar” above the letters:

=sum(A1:B1)

You essentially wrote a little bit of spreadsheet code, though without knowing that you had! While you got here by using your mouse, you can in fact achieve the same thing by typing those exact same characters into another cell (try it!)

Try another formula now in a different cell, say in A3 – perhaps the average of those two numbers in cells A1 and B1. Without looking at the final formula that gets filled in, try to guess what it will look like. If you guessed:

=average(A1:B1)

you are starting to get the hang of this, but don’t worry if you didn’t.

The important thing to notice here is that you can do a lot of the math in a spreadsheet, without using the mouse and menus at all. While it takes some amount of time to first learn how to use a spreadsheet product – what the menu items mean, what types of mathematics you can do, etc. – there’s a whole another layer of coding hidden beneath that one.

The second thing to notice here is the way in which those formula are written. If you guessed the second one correctly, the chances are you saw some of these patterns:

  • A formula must start with the ‘=’ sign
  • The name of the formula is immediately followed by an opening parenthesis (the ‘(‘ symbol)
  • The names of the cells involved are separated by a colon.

This specific set of patterns is called the syntax of the language. It’s similar to a set of patterns you learn when you use the menu options – that too is a syntax, a set of rules about which actions to take in sequence that achieve certain results.

This also tells us that when doing math in a spreadsheet, the cell locations are important because the formulae use these cell locations to refer to the values in the cells. Note that the formula didn’t have the actual numbers from the cells in it – instead, it had the names of the cells.

If you go back now into either A1 or B1 and change the values, then the cell that has the formula in it will also change accordingly. You don’t have to modify the formula cell any more. The “meaning” of the formula then is, “Compute the sum of A1 and B1, whatever the current values in those cells are.

In our next lesson, we will dig a bit deeper into the concept of the formula syntax.