# Functions in Spreadsheets

Spreadsheets come with many built-in functions. Most of these functions come with a spreadsheets, although they may have different names. In this section, we will see how to use some of the functions.

Functions usually take *arguments* (also known as
*parameters*). Typically, an argument is
a value or a range
of cells, which may be a block of adjacent cells or a list of
individual cell
addresses; or an argument may be other information needed by the
function.
For more details about a specific function,
use your spreadsheet's HELP.

We use a function by writing its name followed by a pair of
parentheses; the parentheses contain the arguments to the function.
When we write this we say that we
*call* the function. Once it has performed the
computation it represents, there will be a single value produced, and we
say that the function *returns* this value. The
returned value may be used part of a longer computation; or if the
function call is the entire expression in a particular cell, its return
value is displayed as the value of that cell.

**COUNT(...)** and
**SUM(...)** The COUNT function counts the number of
cells in a range that contain numbers. It does provide any total—just
the count. It ignores blanks and
non-numeric
values. If only
three cells in the
block A1:A15 contain numbers, for
instance, then COUNT(A1:A15) returns 3. The
SUM function finds
the sum of the
numbers in a range of cells. It
ignores
blank cells and cells with non-numeric values.

For example, suppose a spreadsheet looks like this.

A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|

1 | 3 | 6 | 4 | 10 | ||||

2 |

and we enter =COUNT(A1:E1) in cell F1 and =SUM(A1:E1) in cell G1. The result will display as

A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|

1 | 3 | 6 | 4 | 10 | 4 | 23 | ||

2 |

Cell F1 displays the count of cells in A1:E1 that contain numbers. There are four such cells. Cell G1 displays the sum of those cells.

Now suppose we enter the expression =SUM(A1:E1) / COUNT(A1:E1) in cell H1.

A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|

1 | 3 | 6 | 4 | 10 | 4 | 23 | 5.75 | |

2 |

**AVERAGE(...)**,
**MEDIAN(...)** These two functions find the *arithmetic
mean* and the *median*, respectively, of cells
containing numeric data. Suppose cells A1 through A9 contain the
data

A | B | |
---|---|---|

1 | 10 | |

2 | 25 | |

3 | −8 | |

4 | 16 | |

5 | 25 | |

6 | ||

7 | 15 | |

8 | 9 | |

9 | 4 |

In this spreadsheet, the function call =AVERAGE (A1:A9) returns 12. This is equivalent to the expression =SUM(A1:A9) / COUNT(A1:A9).

The function call =MEDIAN (A1:A9) returns 12.5, the median of the data. The median of a set of numbers is the value that would be in the middle of the list if the numbers were arranged in ascending order; if there are two such numbers (as in this case, where 10 and 15 would together be the middle two numbers in the list), the median is the mean of those two middle numbers.

**MAX(...)**,
**MIN(...)** These
functions find the maximum and
minimum, respectively, of numeric data. They ignore any non-numeric
content. For instance, =MAX(C5:E6) returns
the largest numeric value in cells C5,
D5, E5,
C6, D6, and E6.

A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

3 | 98 | 90 | 86 | 60 | 76 | 79 | 85 | 90 | 98 | 67 | 87 | 88 | 83 | 90 | |||

4 |

- We need to sum the data. SUM(C3:P3) does that. However, the sum includes the lowest grade.
- Drop the lowest grade by subtracting it from the sum: SUM(C3:P3) − MIN(C3:P3). Now we have the numerator of the average.
- We need the number of remaining grades. Count the items and subtract 1, since we dropped a grade: COUNT(C3:P3) − 1. This is the denominator of the average.
- Finally, divide the numerator by the denominator. Be sure to put pairs of parentheses around the numerator and around the denominator to make sure they are calculated before the division is done.

## Exercises

Exercises 1 through 6 use the following block of a spreadsheet.A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|

3 | Hours | 12 | 10 | 8 | 10 | 9 | 13 |

- =COUNT(A3:I3)
- =SUM(A3:I3)
- =MAX(A3:I3)
- =MIN(A3:I3)
- =AVERAGE(A3:I3)
- =MEDIAN(A3:I3)

- =SUM(A1:A80)
- =COUNT(A1:A80)
- =MIN(A1:A80)
- =MAX(A1:A80)
- =MEDIAN(A1:A80)
- =AVERAGE(A1:A80)

- The number of numeric items in the first 15 cells of the first row.
- The sum of
the numbers in the third through the 50
^{th}cells of the first column. - The average of the data in the first ten cells of the first column.
- The largest number in the first 60 cells in the third row.
- The average hourly temperature, where the hourly temperatures are in cells A1 through A24.
- Same as exercise 17, except drop the high temperature.
- Same as exercise 17, except drop the low temperature.
- Same as exercise 17, except drop the high and low temperatures.
- There are ten judges at an international diving competition. Their scores for a contestant are in the first row of a spreadsheet. To find the average score, the rules state that the low and high scores are eliminated and the remaining scores averaged. Write an expression for the average score.
- The first
term of a sequence of numbers is 10,
and
each term after the first is half the preceding term. Produce a spreadsheet with the number of each
term in the column A, the term in column B, and the sum of all the
terms
computed so far in column C. The first three rows should display as
follows:
A B C 1 1 10 2 2 5 15 3 3 2.5 17.5 4 - Same as exercise 22, except each term after the first is 6/5 of the preceding term.

## Credits and licensing

This article is by Robert P. Webber, Scott McElfresh, and Don Blaheta, licensed under a Creative Commons BY-SA 3.0 license.

Version 2017-Jan-30 04:45