Using Formulas and Functions

Excel lets you enter just about any type of algebraic formula into any cell. You can use these formulas to add, subtract, multiply, divide, and perform any nested combination of those operations.

Creating a Formula

Excel knows that you're entering a formula when you type an equal sign (=) into any cell. You start your formula with the equal sign and enter your operations after the equal sign.

For example, if you want to add 1 plus 2, enter this formula in a cell: =1+2. When you press Enter, the formula disappears from the celland the result, or value, is displayed.

Basic Operators

Table 14.1 shows the algebraic operators you can use in Excel formulas.

Table 14.1. Excel Operators

Operation	Operator
Add	+
Subtract	-
Multiply	*
Divide	/

So if you want to multiply 10 by 5, enter =10*5. If you want to divide 10 by 5, enter =10/5.

Including Other Cells in a Formula

If all you're doing is adding and subtracting numbers, you might as well use a calculator. Where a spreadsheet becomes truly useful is when you use it to perform operations based on the contents of specific cells.

To perform calculations using values from cells in your spreadsheet, you enter the cell location into the formula. For example, if you want to add cells A1 and A2, enter this formula: =A1+A2. And if the numbers in either cell A1 or A2 change, the total will automatically change, as well.

An even easier way to perform operations involving spreadsheet cells is to select them with your mouse while you're entering the formula. To do this, follow these steps:

1.	Select the cell that will contain the formula.
2.	Type =.
3.	Click the first cell you want to include in your formula; that cell location is automatically entered in your formula.
4.	Type an algebraic operator, such as +, -, *, or /.
5.	Click the second cell you want to include in your formula.
6.	Repeat steps 4 and 5 to include other cells in your formula.
7.	Press Enter when your formula is complete.

Quick Addition with AutoSum

The most common operation in any spreadsheet is the addition of a group of numbers. Excel makes summing up a row or column of numbers easy via the AutoSum function.

All you have to do is follow these steps:

1.	Select the cell at the end of a row or column of numbers, where you want the total to appear.
2.	Click the AutoSum button, shown in Figure 14.2, on the Standard toolbar. Figure 14.2. Use AutoSum to automatically add a row or column of numbers.

Excel automatically sums all the preceding numbers and places the total in the selected cell.

Other AutoSum Operations

Excel's AutoSum also includes a few other automatic calculations. When you click the down arrow on the side of the AutoSum button, you can perform the following operations:

• Average, which calculates the average of the selected cells

• Count, which counts the number of selected cells

• Max, which returns the largest value in the selected cells

• Min, which returns the smallest value in the selected cells

Using Functions

In addition to the basic algebraic operators previously discussed, Excel also includes a variety of functions that replace the complex steps present in many formulas. For example, if you wanted to total all the cells in column A, you could enter the formula =A1+A2+A3+A4. Or, you could use the SUM function, which lets you sum a column or row of numbers without having to type every cell into the formula. (And when you use AutoSum, it's simply applying the SUM function.)

tip

When you're referencing consecutive cells in a formula, you can just enter the first and last number or the series separated by a colon. For example, cells A1 through A4 can be entered as A1:A4.

In short, a function is a type of pre-built formula.

You enter a function in the following format: =function(argument), where function is the name of the function and argument is the range of cells or other data you want to calculate. Using the last example, to sum cells A1 through A4, you would use the following function-based formula: =sum(A1,A2,A3,A4).

Excel includes hundreds of functions. You can access and insert any of Excel's functions by following these steps:

1.	Select the cell where you want to insert the function.
2.	Select Insert, Function to display the Insert Function dialog box (shown in Figure 14.3). Figure 14.3. Choose from hundreds of functions in the Insert Function dialog box.
3.	Select a function category from the Select a Category list, then select a specific function. (Alternatively, you can use the Search for a Function box to search for a specific type of function.)
4.	Click the OK button.
5.	If the function has related arguments, a Function Arguments dialog box is now displayed; enter the arguments and click OK.
6.	The function you selected is now inserted into the current cell. You can now manually enter the cells or numbers into the function's argument.