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. 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. We’ll
refer to a function by its name followed
by a set of parentheses to signify the arguments.
For more details about a specific function,
use your spreadsheet’s HELP.
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 be
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
|
3 |
6 |
4 |
10 |
4 |
23 |
|
|
2 |
|
|
|
|
|
|
|
|
Cells
A1 through E1 contain
data.
Cell
F1 contains the
formula =COUNT(A1:E1)
Cell
G1 contains the
formula = SUM(A1:E1)
Cell F1 contains the count of cells in
A1:E1
that contain numbers.
There are
four such cells. Cell
G1 contains the sum
of those cells.
Now suppose we enter the formula
=SUM(A1:E1) / COUNT(A1:E1)
in cell H1.
The result will be 5.75,
the
arithmetic mean of the data.
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
|
3 |
6 |
4 |
10 |
4 |
23 |
5.75 |
|
2 |
|
|
|
|
|
|
|
|
Cell
H1 contains the
formula =SUM(A1:E1)
/ COUNT(A1:E1).
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
10 25
-8 16 25
15 9 5
=AVERAGE (A1:A9)
returns 12. This is equivalent to the
formula =SUM(A1:A9)
/ COUNT(A1:A9).
=MEDIAN (A1:A9)
returns 15,
the median of the
data.
For example, suppose a grade book
contains student quiz
scores in cells C2:C14. The mean quiz grade would
be
=AVERAGE
(C2:C14)
and the median score would be
=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 value in cells C5,
D5, E5,
C6, D6, and E6.
Suppose that cells
C3:P3 contain
quiz scores for a
student, and we want to drop the lowest quiz score and find the average
of the
remaining grades. Here’s
the row of the
spreadsheet.
|
|
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
Here’s the required formula.
=(SUM(C3:P3)
– MIN(C3:P3)) / (COUNT(C3:P3) – 1) ,
and here’s the answer.
|
|
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 |
85.92308 |
|
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Cell
Q3 contains the formula =(SUM(C3:P3)
– MIN(C3:P3)) / (COUNT(C3:P3) –
1)
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 is displayed by each function?
1.
=COUNT(A3:I3)
2.
=SUM(A3:I3)
3.
=MAX(A3:I3)
4.
=MIN(A3:I3)
5.
=AVERAGE(A3:I3)
6.
=MEDIAN(A3:I3)
In Exercises 7 through 12, describe
in words what is
displayed by the function.
7.
=SUM(A1:A80)
8.
=COUNT(A1:A80)
9.
=MIN(A1:A80)
10.
=MAX(A1:A80)
11.
=MEDIAN(A1:A80)
12.
=AVERAGE(A1:A80)
In exercises 13 through 29, write a
formula to display the
desired result.
13.
The number of
numeric items in the first 15 cells of the first row.
14.
The sum of
the numbers in the third through the 50th
cells of the first
column.
15. The average of the data in the first ten cells of the first column.
16. The largest number in the first 60 cells in the third row.
17.
The average
hourly temperature, where the hourly temperatures are in cells A1
through A24.
18.
Same as
exercise 17, except drop the high temperature.
19.
Same as
exercise 17, except drop the low temperature.
20. Same as exercise 17, except drop the high and low
temperatures.
21.
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
a formula for the
average score.
22.
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. Here
are
the first several rows.
|
|
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?
23.
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 and Scott McElfresh, licensed under a Creative Commons BY-SA 3.0 license.
Version 2015-Sept-01 11:00