Google Sheets – Formulas & functions are your friends!

Cells may be the building blocks of a spreadsheet but functions and formulas are the power behind it. They are there to help you with the hard work of calculations and to avoid having to do things manually. Whether it’s a simple addition formula or highly complicated logical and mathematical functions, there are hundreds of functions to choose from and these can be combined in almost unlimited ways. However, even most power users end up only using a select few repeatedly.

So, what are Google Sheet formulas?

Let’s start with a simple example. Let’s add up four numbers (1, 3, 2, 4), in this example the goals scored by 4 players in a game.

To add these up on the sheet, I type in =1+3+2+4 and press Enter to get the result. The equals sign (=) is what tells the sheet that it’s a formula and not just some text.

Sheets4 - 1    Sheets4 - 2

I can also do this by referring to the cells the numbers are in, i.e. =B2+B3+B4+B5 then press Enter.

Sheets4 - 3     Sheets4 - 4

Clicking on B6, you can see the formula in the formula bar.

Now, imagine if I had 100 players, the formula would be pretty long to work out the total! =B2+B3+B4+…..+B101

Well, this is where a function can help out.


What are functions?

Functions are a collection of different keywords that will do specific jobs. Functions have a certain syntax that need to be used. They start with an equals sign (like formulas do) and then they have a pair of brackets. Inside the brackets is where you define what you want the function to do.

=sum()

The one we want in this example is the sum function, as we want to sum up the goals scored. In cell B6 let’s type in the sum function to sum up the total goals scored:

=sum(B2:B5)

This is telling the sheet, sum up the range from B2 to B5 (i.e. the goals scored in game 1).

Sheets4 - 5    Sheets4 - 6

If I had 100 players, the function would be the same size and something like this:

=sum(B2:B101)

Much better than typing in 100 cell references!


Now we know how to write a function, let’s look at some more common ones.

In this example, we’ll look at analyzing some exam results. I’ve already entered in the students’ exam results and now I want to know the average mark, the highest and the lowest mark.

Sheets4 - 7

=average()

Underneath the exam results, I’m going to add the average mark.

In cell B13, type in =average(B3:B12) and press Enter

This gets the range from B3 to B12 and works out the average mark (the mean). This is much quicker and simpler than adding up all the marks individually and then dividing by the total number of students (i.e. (B3+B4+….+B11+B12)/10)

Sheets4 - 8     Sheets4 - 9

=max()

Now, let’s find the highest mark.

In cell B14 type in =max(B3:B12) and press Enter

Sheets4 - 10     Sheets4 - 11

=min()

And let’s find the lowest mark.

In cell B15 type in =min(B3:B12) and press Enter

Sheets4 - 12      Sheets4 - 13

Great! With three simple functions, we now have a good idea of how well the class is doing.


=count() and counta()

In this final example, I want to work out the percentage of students that sat an exam.

This has three parts to it, and will introduce you to not only two more functions but to how functions can work together to give you the information you need.

So, what I need to work out is:

the number of students who sat the exam divided by the total number of students and display that number as a percentage

Sheets4 - 15

First, let’s find out how many students sat the exam.

In B12, type in =count(B2:B11) and as always press Enter

The count function, adds up all the numbers in the range selected. It ignores the text “not present”. So, here 7 students sat the exam.

Sheets4 - 16     Sheets4 - 17

Next, let’s add up the total number of students.

In B13, type in =counta(A2:A11) and press Enter

This time, the counta function adds up all cells which aren’t blank, in the range selected. It doesn’t matter what type of data is in the cells. So, it’s correctly worked out that they are 10 students because there are 10 names in that range.

Sheets4 - 18   Sheets4 - 19

Finally, I want to know the percentage of students who sat the exam, so I need to divide the number sat into the total, i.e. cell B12 divided by B13.

In B14, type in =B12/B13 and press Enter

Sheets4 - 20  Sheets4 - 21

To turn the number in cell B14 into a percentage, click on B14 then click on the percentage (%) button on the toolbar .

Sheets4 - 22

Sheets4 - 23


If you feel comfortable with the above, you could have quickened the process by putting the function, that you entered into B12 and the one into B13, together and this would give you the same result. Note, you only enter the equals sign at the start of the formula.

=count(B2:B11)/counta(A2:A11)


All of the functions above (except counta) can also be quickly accessed via the menu shortcut toolbar, by clicking on the sigma button and a drop-down menu appears with the 5 most common ones.

Sheets4 - 24

Sheets4 - 25

This starts you off with the function and all you need to do is add the cells or range it needs to use.

Sheets4 - 26

Notice that when you start typing a function or use the shortcut above, Sheets provides you with some help. In the first row, it’s telling you what kind of data it expects in the function. The second row, gives you an example of how to fill it out. The third row tells you what the function does. And the final rows, give you more information on the data you need to include within the brackets.

All of this is extremely useful, especially when using a function for the first time or indeed when you can’t quite remember what data it needs inside it.


You may have also noticed that while you write out the function or formula, a little “speech bubble” appears telling you the result of your formula so far. It’s a great way to check your formula is producing the result you’re expecting, as you’re writing it.

Sheets4 - 14


Clicking on “more functions” in the sigma button above, takes you to the full list of functions. Here’s the link:

https://support.google.com/docs/table/25273?hl=en-GB&rd=2


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


One thought on “Google Sheets – Formulas & functions are your friends!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s