Tutorial: Spreadsheet Functions Part 1

If you love statistics/numbers about books as much as I do, then you’re going to love this one.
The list of functions that I regularly use and want to show you is quite long, so this tutorial is split into 3 parts.

I thought it would be especially fun if I chose an example everyone can do themselves. That’s why I am going to use my GR shelf as example data.

If you want to try all this yourself:

• download your GR shelves as an csv file here
• import that file into a new google spreadsheet (File > Import… > Upload)
• rename the tab to “GR”
• add a second tab to write the functions in

I’m going to explain all functions based on this setup!

 

As this is only the first tutorial, I’m going to start with a few basic functions. Maybe you already know how to handle these than you should come back for the next one, or even the third when I show you examples on how to combine them.

Defining data ranges

Probably the most important thing because you have to define ranges all the time in function.

• a row of values (=letters for start and end column, fixed row number)
  Example 1: A1:H1
  Example 2: B1:1 (until the end of the row)
  Example 3: 1:1 (complete row)
• a column of values (=fixed column letter, numbers for start and end row)
  Example 1: A1:A100
  Example 2: A2:A (until the end of the column)
  Example 3: A:A (complete column)
• when referencing a range of a different tab, you have to put the name of the tab followed by an exclamation mark in front of the range (that’s why it is good to use short names!)
  Example: GR!A:A

 

Arrayformula

I list this one first because I use it a lot and I also want to show you a few examples where you can combine this with other functions. You need to use the arrayformula if you don’t want an output of a single value, but a new array of values. 

Example:

compute the average number of days a book has been on your TBR. You can do that by calculating “Date Read” minus “Date Added”. If you would only do it for one book, for example the first one, the formula would be:
=GR!O2-GR!P2

To get this subtraction for all values, we need the arrayformula:
=ARRAYFORMULA(GR!O2:O – GR!P2:P)

When you put that formula somewhere, you’ll get a whole column of values. With those values you can now calculate even more, e.g. the average:
=AVERAGE(ARRAYFORMULA(GR!O2:O – GR!P2:P)) 
=ARRAYFORMULA(AVERAGE(GR!O2:O – GR!P2:P)) also works

 

Filter

filter(output_range, filter_criterion)
Filter is another great function when you want more than just one value as an output. 

Example:

You can filter all the books on your GR shelves to only display the ones that you have read (Date Read is >0)

• Get all rows:
  =FILTER(GR!A2:AE,GR!O2:O>0)
• Get only title and author:
  =FILTER(GR!B2:C,GR!O2:O>0)
• Get the the number of days between added and read:
  =FILTER(GR!O2:O-GR!P2:P,GR!O2:O>0)

(in a later tutorial I’ll also go into the query function that can do a lot more than the filter function)

 

Counting

There are a few functions that you can use to count entries:

• count(range): counts number of numeric values in a range
• counta(range): counts the number of values in a range
• countblank(range): counts the number of empty cells in the range
• countif(range,criterion):  counts how many cells fullfill a certain criterion
• countunique(range): counts number of unique values

Examples:

• count how many books have a value for the number of pages (column L)
  =COUNT(GR!L2:L)
• count how many books have an additional author (column E)
  =COUNTA(GR!E2:E)
• count the number of books that are missing a value for number of pages (column L)
  =COUNTBLANK(GR!L2:L)
• count the number of books that have more than 500 pages
  =COUNTIF(GR!L2:L,”>500″)
• count how many books got a rating of 5
  =COUNTIF(GR!H2:H,5)
• count how many books are by Patrick Ness
  =COUNTIF(GR!C2:C,”Patrick Ness”)
• count how many different authors are on your shelf
  =COUNTUNIQUE(GR!C2:C)

Important for the criterion: if you have a numberic value you want to compare to, you can just insert that value, but everything else needs quotes around it. 

 

Sum

• sum(range)
• sumif(range, criterion,[sum_range])
  (sum_range is only needed if you want to check the criterion against one range, but the sum over another)

Examples:

• Number of pages overall
  =SUM(GR!L:L)
• Number of pages read
  =SUMIF(GR!O:O,”>0″,GR!L:L)
• Number of pages read in 2014
  =ARRAYFORMULA(SUMIF(YEAR(GR!O:O),“2014”,GR!L:L))

 

Average

• average(range)
• averageif(range,condition,[average_range])

Examples:

• the average number of pages per book
  =AVERAGE(GR!L:L)
• the average rating per book (exclude books without a rating = rating > 0)
  =AVERAGEIF(GR!H:H,”>0″)
• the average number of days on TBR (exclude books not read yet = date read >0)
  =AVERAGE(FILTER(GR!O2:O-GR!P2:P,GR!O2:O>0))