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.
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.
Just replace MAX by MIN to get the smallest book on your 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:
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*”.
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.
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:
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)]
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”.
Feel free to ask questions in the comments.
For more Code & Design Tutorials see this page.