2.2 Introductory Statistical Functions
Learning Objectives
- Use the SUM function to calculate totals.
- Use the COUNT function to count cell locations with numerical values.
- Use the AVERAGE function to calculate the arithmetic mean.
- Use the MAX and MIN functions to find the highest and lowest values in a range of cells.
- Learn how to copy and paste formulas without formats applied to a cell location.
- Use absolute references to calculate percent of totals.
- Learn how to set a multiple level sort sequence for data sets that have duplicate values or outputs.
In addition to formulas, another way to conduct mathematical computations in Excel is through functions. Excel functions apply a mathematical process to a group of cells in a worksheet. For example, the SUM function is used to add the values contained in a range of cells. Functions are more efficient than formulas when you are applying a mathematical process to a group of cells. If you use a formula to add the values in a range of cells, you would have to add each cell location to the formula one at a time. This can be very time-consuming if you have to add the values in a few hundred cell locations. However, when you use a function, you can highlight all the cells that contain values you wish to sum in just one step.
The components of a function are as follows:
=FunctionName(Arguments)
Functions are a type of formula, therefore they start with an equal sign. The next component is the name of the function. A list of commonly used functions is shown in Table 2.4. After the function name comes the arguments for the function, which are always enclosed in parentheses. The arguments are the cell locations and/or values that will be used in the function. The number and type of arguments varies based on the the function being used, although in this section we will only work with a range of cells for the function arguments. Some examples of different functions with their arguments are:
=SUM(B2:B15) – adds the values in B2 through B15
=SQRT(A5) – finds the square root of the value in A5
=COUNTA(A1:A20) – finds the number of cells from A1 through A20 that contain text or a number
Throughout Section 2.2 we will add a variety of mathematical functions to the Personal Budget workbook. In addition to creating functions, this section also reviews percent of total calculations and the use of absolute references.
Table 2.4 Commonly Used Functions
| Function | Output |
| ABS | The absolute value of a number |
| AVERAGE | The average or arithmetic mean for a group of numbers |
| COUNT | The number of cell locations in a range that contain a numeric value |
| COUNTA | The number of cell locations in a range that contain text or a numeric value |
| MAX | The highest numeric value in a group of numbers |
| MEDIAN | The middle number in a group of numbers (half the numbers in the group are higher than the median and half the numbers in the group are lower than the median) |
| MIN | The lowest numeric value in