Google Sheets 101: Functions

LESSONS

Functions are the lifeblood of spreadsheets.

They're amazing little tools that take data in, calculate or manipulate it, and return the new data you need.

A function in a formula bar looks like this:

=FUNCTION(data1,data2)

You'll get to know the name of many functions from the hundreds available to you.

Some of the more common functions include SUM, AVERAGE, COUNT, MIN, MAX.

The data points you feed these functions (e.g. data1,data2) are called 'arguments'.

Functions can require a certain number of arguments (including none at all), others can take as many arguments as needed, and some have optional arguments that are only required for the function to work in a specific way.

SUM

Let's use the SUM function as an example.

SUM returns the sum of a series of numbers and/or cells.

SUM can take numbers, cell references, cell ranges, or a combination of all three.

Sample Usage

	A	B
1	Numbers	=SUM(1,2,3,4,5)
2	Cell references	=SUM(A1,A2)
3	Cell ranges	=SUM(A2:A100)
4	Combination	=SUM(1,A2,A4:A50)

Syntax

=SUM(value1, [value2, ...])

The square brackets around [value2, ...] mean it's optional.

The ellipsis (...) at the end means that SUM can take as many arguments as you need. The only required argument is the first one.

Functions within functions

The beauty of spreadsheet functions is that they can be ‘nested’.

This means an argument for one function can be the output of another.

Let’s use the following sales data as an example:

	A	B	C	D
1	Salesperson	Jan	Feb	Mar
2	Jim Halpert	$5,000	$7,000	$5,500
3	Dwight Schrute	$6,000	$6,500	$5,900
4	Andy Bernard	$1,000	$3,000	$2,500
5	Stanley Hudson	$4,000	$5,500	$5,750
6	Phyllis Vance	$3,000	$6,000	$6,000

You want to get the total sales for the three months.

You could do this using any of the following (with the last being the easiest to understand and therefore the best):

=SUM(B2,B3,B4,B5,B6,C2,C3,C4,C5,C6,D2,D3,D4,D5,D6)
=SUM(B2:B6,C2:C6,D2:D6)
=SUM(B2:D6)

You could also do the following:

=SUM(SUM(B2:B6),SUM(C2:C6),SUM(D2:D6))

This shows what nesting looks like.

Each argument to the outer SUM function is itself a sum function. You’re summing the sum of each year’s sales figures.

The internal SUM functions SUM(B2:B6),SUM(C2:C6),SUM(D2:D6) are executed first with the values they return then used are arguments to the outer SUM function: =SUM(19000, 28000, 25650).

It’s not the best way to use the sum function, but it shows that the arguments of one function can be the output of another.

Function Exercises

Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.

1. Numbers

In cell B8, enter a formula that uses a function to calculate the sum of Dwight and Jim's sales using numbers (not references). Remember to start your formula with '='.

2. Cells

In cell B9, enter a formula that uses a function to calculate the sum of Dwight and Jim's sales using cell references (not ranges).

3. Ranges

In cell B10, enter a formula that uses a function to calculate the sum of everyone's sales using a range reference (not cells).

Current question: See the correct formula or clear the formula bar.