One of the real strengths of spreadsheets is their ability to harness the power of formulas. In fact, you might say that formulas were the reason spreadsheets were invented.
The keys to effective use of formulas in Excel are its built-in functions. Functions handle complex calculations automatically for the spreadsheet designer. In this article, you’ll learn about five very common Excel functions (AVG, SUM, COUNT, MIN, MAX) and how to apply them.
But first, in order to use functions or formulas, you must know how Excel handles cell references.
Cell References
A cell reference tells Excel where the data it needs for a calculation is located. There are three ways to make a cell reference in an Excel formula.
Most of the time Excel formulas use relative cell references. Relative cell references are made up of the column letter and the row number. For example, B3 is a relative reference for the cell located in the second column from the left edge of the spreadsheet, on the third row. Columns are lettered from left to right, starting with A. Rows are numbered from the top down, starting with 1 (see Figure 1).
Figure 1: B3 is a relative reference
B3 is a relative reference because the cell’s location is “relative” to any cell containing a formula that uses the contents of that cell. A relative reference will update itself when the formula it is in is moved to another cell.
To make this a little clearer, suppose you created a formula in cell G3 to add the values in all the cells in the third row between the third and the sixth columns. The instructions to Excel would look like this in part: (C3+D3+E3+F3).
To place this formula in cell G3, you would select cell G3 by clicking in it. You would tell Excel that the expression in this cell is a formula. Do this by typing an equal sign (=) into the cell or into the Formula Bar at the top of the spreadsheet. You would then enter the formula by typing it into the cell or the Formula Bar (see Figure 2).
Figure 2: The formula entered in cell G3
On the next row down, suppose you also want to total the cells in columns C through F. Rather than retype the whole formula, just select G3, select Copy from the Edit menu, and then select G4. Open the Edit menu and select Paste.
When you copy the formula from cell G3 to cell G4, Excel automatically adjusts the relative references to correct for being a row lower down. In cell G4, the instructions to Excel will update to (C4+D4+E4+F4) without any further work on your part (see Figure 3).
Figure 3: The updated relative reference
Sometimes you will want to have the cell references stay the same when the formula is copied. For example, suppose you have a worksheet in which you will total the daily cost of one of the raw materials used to produce widgets during a one-month period. It would be faster to set up the worksheet if you didn’t need to enter the unit cost thirty times.
It is much simpler to have constants in only one place and copy the formulas from cell to cell. You can do this and still have everything come out right if you use either absolute or mixed cell references (see Figure 4).
Figure 4: Using absolute cell references
An absolute cell reference does not change when a formula is copied from one cell to another. In a mixed cell reference, either the row or the column in the reference is made absolute, and the other part is relative.
Absolute cell references are created by placing dollar signs in front of the column letter and row number. For example: $B$3 is an absolute reference to the cell in the second column of the third row on a given worksheet. This reference will always point to that cell, even if the formula containing the reference is copied or moved to another cell.
$B3 is a mixed reference: B is absolute, 3 is relative. It will always point to the same column when the formula it is in is moved or copied to another cell, but the row will update.
Although cell references are designated as relative, absolute, or mixed at the time they are created, they can be easily changed to another type at any time.
To cycle between relative, absolute, and mixed cell references, place the insertion point within a cell reference in the formula bar (not in front of the equal sign) and then press the F4 key repeatedly until you get the reference format you require.
Basic Functions (AVERAGE, SUM, COUNT, MIN, MAX)
Functions are formulas that are stored in Excel. Excel has more than 200 built-in functions for many different kinds of computational problems. This section addresses use of the five most basic functions:
- The AVERAGE function calculates the average, or arithmetic mean, of the values in a specific set of cells
- The SUM function calculates the total of the values in a set of cells
- The MIN and MAX functions find the minimum and maximum values contained in a set of cells
- The COUNT function determines how many number values are contained within a designated set of cells
You can enter functions into specific cells on worksheets in three ways. You can type them directly into either the cell or the formula bar. You can also use a special method, called Paste Function. Paste Function will be addressed at another time.
A function requires values, called arguments, which it uses to come up with a result. For example, the SUM function must have values to add. If those values are stored in cells of the spreadsheet, it must have the cell references. The arguments are placed inside parentheses after the function name.
In their most basic form, arguments can be constants (numbers) or cell references. Arguments can also be other functions. If a function can use more than one argument, commas are used to separate the arguments. The value that the function returns is called the result.
Cell references are often simply typed into the formula. You can also click and drag the cell references from the worksheet.
Begin by typing an equal sign (=). Then type the name of the function, and the left parenthesis; for example, =SUM(.
Type in the arguments that may need to appear, such as numbers or cell references, separated by commas. If the arguments are values in a set of cells that are all next to each other in one column or row, click on the first cell in the set. Then drag across or down the range to the last cell, and press the Enter key or the Tab key. This last action enters the selected range of cells, inserts the final parenthesis, and causes the result of the function to be displayed.
If arguments must be changed after this point, select the cell where the function is stored and change (add, modify, remove) the argument in the formula bar.
The AVERAGE Function
The AVERAGE function calculates the average, or arithmetic mean, of a set of values. The syntax, or format, for this function is =AVERAGE(arguments). This is the formula that is assigned to the cell where you want the result to be displayed.
The arguments can be cell references, constants, or any combination of these. The AVERAGE function ignores text, blank cells (but not zeros), error values, and logical values (TRUE or FALSE).
=AVERAGE (2,D3:D5) will average the constant value 2 with the three values stored in cells D3, D4, and D5. The expression =AVERAGE(2,3,4) will find the mean of 2, 3, and 4, giving a result of 3. If you enter =AVERAGE(F16,H6), the result will be the average of the values stored in those two cells.
The SUM Function
The SUM function calculates the total of a set of values. The syntax for SUM is =SUM(arguments). Like AVERAGE, the arguments for SUM may be cell references, constants, or some combination of the two. SUM also ignores text, blank cells (not zeros), error values, and logical values.
=SUM(2,D3:D5) will add the constant value 2 and the values stored in cells D3, D4, and D5. The expression =SUM(2,3,4) will find the total of 2, 3, and 4, giving a result of 9. If you enter =SUM(F16,H6), the result will be the total of the values stored in those two cells.
The MIN and MAX Functions
The MIN and MAX functions find the minimum and maximum values in a set of values. Their syntax is =MIN(arguments) and =MAX(arguments). The arguments may be cell references, constants, or a combination, for which you want to find the smallest or largest value. Both of these functions ignore text, blank cells (not zeros), error values, and logical values.
=MIN(5,G7,H8) will compare the value 5 and the values in cells G7 and H8, and will return the smallest value of the three. =MAX(5,G7,H8) will return the largest value.
The COUNT Function
The COUNT function determines how many number values are contained within its arguments. If an argument is a cell or a range of cells, COUNT identifies how many of the values in that range are numeric. The syntax for COUNT is =COUNT(arguments). The arguments that COUNT takes are cell references, constants or some combination of the two. Like the other functions discussed here, COUNT ignores text, blank cells (not zeros), error values, and logical values.
If B2 contains the number 5, B3 the logical value TRUE, B4 the number 74, and B5 the text NO, then =COUNT(B2:B5) will return the value 2. This is because 2 cells contain number values.
As noted, any of these functions can take other functions as arguments. So the expression =(SUM(C2:C4,AVERAGE(B3,G5)) will find the average of the values in cells B3 and G5, and will add this to the sum of the values in cells C2, C3, and C4.
Functions are extremely powerful features in Excel. In future articles here, we will continue to explore what can be done with them.
