Tutorial: Spreadsheet Functions Part 2

Make sure to read Part 1 of the tutorial, as it explains the setup I work with (my GR shelves) that helps you to better understand what I’m doing!

The first tutorial was more of an introduction of the basic functions that you need/use all the time. Now I’m going a bit further and explain some of the more complicated ones that can come in quite handy and even more important: how to get some neat results by combining them.

Find the biggest book on your shelf

This is something I love to have in my yearly spreadsheets. There are different steps and functions that are needed to get the title of a book with the biggest value in number of pages.

1. Find the biggest value in the number of pages column (L)
   =MAX(GR!L:L)
2. Get the row this value is in. This is done by using the MATCH function, which finds the position (row) of a search key in a search range
   =MATCH(search_key,search_range,search_type)
   In our case the search_key is the max value, the range has to be the same we used for finding the maximum and the search_type says whether the range is sorted (1) or not (0).
   =MATCH(MAX(GR!L:L), GR!L:L,0)
3. Get the value of the title column (B) for the row that has been found. We use the INDEX function which returns the content of a cell, defined by range and row+column offset.
   =INDEX(range, [rowOffset], [columnOffset])
   As we limit the range to the column of titles, we only need a row offset which is exactly the value we got in step 2
   =INDEX(GR!B:B, MATCH(MAX(GR!L:L), GR!L:L,0))

Just replace MAX by MIN to get the smallest book on your shelf.

Count the number of books on a certain shelf 

If you only consider exclusive shelves (S) where the cells only contain the name of a single shelf, this can be easily done by using COUNTIF:
=COUNTIF(GR!S:S,“read”)

Non-exclusive shelves (R) are in a comma separated list in just one cell though, so that you need to use the trick of a wildcard. (It could also be done with the FIND function, but the wildcard is a lot easier) There are two wildcards you can use: “?” (just one character) and “*” (multiple characters)
Let’s say we want to count the number of books that you have on a shelf called “favorites”. As the cells contain a list, meaning there can be characters before and after the name of this shelf, we need to match “*favorites*”.

=COUNTIF(GR!R:R, “*favorites*”)

Count the number of books on a certain shelf, read in 2015

We take the example above a step further and want to filter the books we count. Again, we want the books on the shelf called “favorite” but only the ones read in 2015. For that, we can use the function above but we need to filter the range (GR!R:R) first.

1. Filter the range to only get books read (date is in O) in 2015:
   =FILTER(GR!R:R, YEAR(GR!O:O)=2015)
2. Use the filtered range to count the books on a shelf:
   =COUNTIF(FILTER(GR!R:R, YEAR(GR!O:O)=2015), “*favorites*”)

Automatically number rows

Most of the time when making spreadsheets, you use the first column to number your rows of data (as it doesn’t equal the actual row number because of the header rows). You could of course always type “1” in the first one and drag the small box to auto fill all the others. That would mean that you always have to update that row though when you add data. Instead you can use a formula that does it for you. (this comes in especially handy when the data is also automatically outputted)

Let’s say we want to start with counting from one in cell A3:

1. Get the current row number and subtract the offset:
   =ROW(A3)-2
2. We only want a number if the row has some data in it, so we check if B3 is empty. If it is blank, we leave the row number blank too, if not, we fill it with what we get from step 1
   =IF(ISBLANK(B3),” “,ROW(A3)-2)
3. Now we want it to automatically expend to all other rows which we can do by using the ARRAYFORMULA
   =ARRAYFORMULA(IF(ISBLANK(B3:B),” “,ROW(A3:A)-2)

This formula checks all rows which makes the spreadsheet slower than it could be. If you know how many rows you’re going to have at a max, use that value!
[E.g. 100 rows: ARRAYFORMULA(IF(ISBLANK(B3:B103),” “,ROW(A3:A103)-2)]

Add text to automatic values

No one really needs this, but I like to make more fancy spreadsheets by adding some text to the values I get. For example: rather than having a cell that contains the number of pages the biggest book on my shelf has, I want it to say “xxx pages”. 

1. Get the number of pages:
   =MAX(GR!L:L)
2. Add some text by concatenating:
   =CONCATENATE(MAX(GR!L:L),” pages”) or
   =CONCATENATE(“The biggest book has “,MAX(GR!L:L),” pages”)

Feel free to ask questions in the comments.

For more Code & Design Tutorials see this page.