Warning: A non-numeric value encountered in /home/customer/www/books4purpose.com/public_html/wp-content/themes/Divi/functions.php on line 5560
949.391.3560 liz@books4purpose.com
Select Page

Basic Math Operators

All spreadsheet programs use standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents. All formulas must begin with an equals sign (=). This is because you want the cell to contain, or equal to, the formula and the value it will calculate.

What is a cell reference?

You can create simple formulas manually (for example, =8+2 or = 100*5). However, most of the time you will use cell addresses to create a formula. Otherwise known as making a cell reference. Using cell references will ensure that your formulas are always accurate, because you can change the value of referenced cells without having to rewrite the formula. By combining a mathematical operator within cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below: ### Creating a formula:

In the following example, I’ll use a simple formula and cell references to calculate the sum of a budget.

Select the cell that will contain the formula. Type the equals sign (=). Notice how it appears in both the cell and the formula bar. Type the cell address of the cell you wish to reference first in the formula. Type the mathematical operator you wish to use. Enter the cell address of the cell you wish to reference second in the formula, cell B2 in this example. Press Enter. The formula will then be calculated, and the value will be displayed in the cell. #### Changing values with cell references

The real advantage of cell references is that they allow you to update data in your spreadsheet without having to rewrite formulas. In the example below, we have changed the value of cell B1 from \$1,200 to \$1,800. The formula in B3 will automatically recalculate and display the new value in cell B3. To create a formula using your mouse:

Instead of typing cell addresses manually, you can use your mouse to point and click on the cells you wish to include in your formula. This can save a lot of time and effort when creating formulas. In the following example below, we will create a formula to calculate the cost of ordering several boxes of plastic silverware.

Select the cell that will contain the formula. • Type the equals sign (=).
• Select the cell you wish to reference first in the formula, cell B3 in our example. The cell address will appear in the formula. • Type the mathematical operator you wish to use. In our example, we are using the multiplication sign (*).
• Click on the cell you wish to reference second in the formula, cell C3 in our example. The cell address will appear in the formula. • Press The formula will be calculated, and the value will be displayed in the cell.

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a spreadsheet. Let’s edit a formula:

Sometimes you may want to modify an existing formula. In the example below, we’ve entered an incorrect cell address in our formula, so we’ll need to correct it.

Select the cell containing the formula you wish to edit. Click the formula bar to edit the formula. You can also double-click the cell to view and edit the formula directly within the cell. A border will appear around any referenced cells. In our example, we’ll change the second part of the formula to reference cell B2 instead of cell C2. When finished, press Enter

The formula will be updated, and the new value will be displayed in the cell. If you change your mind, you can press the Esc key on your keyboard to avoid accidentally making changes to your formula.

### Now you try it…

1. Open an existing Excel workbook.
2. Create a simple addition formula using cell references. If you are using the example, create the formula in cell B4 to calculate the total budget.
3. Try modifying the value of a cell referenced in a formula. If you are using the example, change the value of cell B2 to \$2,000. Notice how the formula in cell B4 recalculates the total.
4. Edit a formula using the formula bar. If you are using the example, edit the formula in cell B9 to change the division sign (/) to a minus sign (-).