Functions

Functions are one of the big things that separate a spreadsheet from a paper ledger. I mean, you can do arithmetic functions on a piece of paper pretty easily. You can use highlighters or different pens to change the “format” of the cells. But functions let you do more complicated things. You can do in a single step what might take many steps to do on paper.

You might remember some common functions from math class, such as sine, square root, and log, and average. Or you might think of job functions, or daily tasks such as “get groceries”. Both of these types of functions are available in spreadsheets.

Function syntax is pretty much the same across all spreadsheets. They start with an equal sign, so the spreadsheet knows it’s not text. Then you have the function name, followed by an open parenthesis. Then you have the parameter(s), then you have a closing parenthesis. For example, =average(a1:a100) might return the average of all the numbers is cells a1 through a100.

Some notes about parameters:

  1. Most functions require at least one parameter. You need to have something in order to take the square root of it, after all!
  2. Some functions require a list of parameters, even if that list consists of only one thing. Think of it like getting groceries. Your shopping list might be a mile long, or it might consist of “milk”, but it’s hard to get groceries without getting at least one thing. (That’s why I’ve used the term “get groceries” for the example, not “go shopping”.) A common example is the concatenate function, which combines all of its parameters into a single string of characters. If you don’t know what a string is, think of it as a word, sentence, or paragraph, depending on how long it is.
  3. A few functions can take a parameter, or have an assumption that they make if you don’t specify a parameter. That’s like telling your kid, “Go outside!”. Your kid can probably safely assume that you mean them, even if you didn’t explicitly say “You go outside!” Likewise, in some cases the spreadsheet will assume it knows what you want if you fail to be explicit. Common examples in spreadsheets are the “row” and “column” functions. They return the row or column of the parameter if you specify the parameter. If you don’t give it a parameter then it’ll return the row or column of the cell the function’s in.
  4. A small handful of functions do not allow you to specify parameters. A common example of this would be the “today()” function, which returns today’s date.