Functions in Spreadsheets

Robert P. Webber, Scott McElfresh, Don Blaheta, Longwood University

Why are we working on this? Many computations you need to do are composed of some fairly standard pieces, called functions, which are provided to you by the application software. Understanding these functions and using them effectively makes your work easier to write and easier for others to understand (and easier to fix if something goes wrong!) Skills in this section: Compute by hand what particular function calls would produce — Write expressions that use builtin functions effectively Concepts: Algorithm representation, Functions and subprocedures

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

(Cells B1 through E1 contain plain numbers.)

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

(Cells B1 through E1 contain plain numbers. Cell F1 contains the expression =COUNT(A1:E1). Cell G1 contains the expression =SUM(A1:E1).)

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

(As before, and also: Cell H1 contains the expression =SUM(A1:E1) / COUNT(A1:E1).)

The cell displays 5.75, the arithmetic mean of the numbers in the given cell range.

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.

For example, suppose a grade book contains student quiz scores in cells C2:C14. The mean quiz grade would be computed with the expression

=AVERAGE (C2:C14)

and the median score would be computed with the expression

=MEDIAN (C2:C14)

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.

Suppose that cells C3:P3 contain quiz scores for a student, as follows:

	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

The function call MAX(C3:P3) would return 98, the largest value in that range (which appears in both C3 and K3, but either one would be sufficient). The function call MIN(C3:P3) would return 60 (which is in F3).

Now, suppose we want to perform a computation that drops the lowest quiz score and finds the average of the remaining grades. Let's analyze this situation carefully. The AVERAGE function won't work, because it averages all the grades, without dropping the lowest one. However, think about how to find an average. The average is the sum of the data, divided by the number of items.

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.

The required expression is thus

=(SUM(C3:P3) − MIN(C3:P3)) / (COUNT(C3:P3) − 1)

which displays as 85.92308.

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

What would be displayed in cells containing each of the following expressions?

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

In the second group of exercises, describe in words what is displayed by the function.

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

In the remaining exercises, write an expression to display the desired result.

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

Continue the spreadsheet for 50 terms of the sequence. What do you notice about the numbers in column B? in column C?
Same as exercise 22, except each term after the first is 6/5 of the preceding term.

	A	B	C
1	1	10
2	2	5	15
3	3	2.5	17.5
4

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