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.
and we enter =COUNT(A1:E1) in cell F1 and =SUM(A1:E1) in cell G1. The result will display as
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.
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
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.
- 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.
ExercisesExercises 1 through 6 use the following block of a spreadsheet.
- The number of numeric items in the first 15 cells of the first row.
- The sum of the numbers in the third through the 50th 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,
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
computed so far in column C. The first three rows should display as
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