In Excel 2016, What are Formulas and Calculations?

## ?

Mathematical Operators

The main ingredient in any formula – aside from the numbers – are the mathematical operators. These operators tell Excel how to calculate a formula.

That said, let's start out with a simple formula, learn how to enter it into Excel, and learn the operators.

The formula we're going to use is:

Now, in the formula above, we want Excel to tell us the sum of 5+2. The numbers 5 and 2 in this equation are called operands. Operands in Excel can be either a number or a cell.

For instance, if we wanted to add the value of two cells, say D1 and D2, we'd write:

When we use a cell name instead of a number, it is called a reference

The + symbol is called an operator. You use operators to tell Excel what kind of calculation you want it to perform. In this case, we want to add the two numbers together to find the sum.

Excel recognizes four different kinds of calculation operators. They are:

• Arithmetic operators--used to perform basic mathematical operations such as adding, subtracting, multiplying, and dividing.

• Comparison operators--used to compare values.

• Text operators--used to join two distinct text strings into a single string. For instance, "global" & "warming."

• Reference operators--used to combine ranges of cells for calculations

The table below contains a list of recognized operators.

 Operator Meaning Arithmetic operators + Addition - Subtraction * Multiplication / Division % Percent ^ Exponentiation Comparison operators = Equal to Interested in learning more? Why not take an online Excel 2021 course? > Greater than < Less than > = Greater than or equal to < = Less than or equal to < > Not equal to Text operator & Connects two values to produce one continuous text value Reference operators : Range operator , Union operator, combines multiple references into one reference. (space) Intersection operator

## Creating a Formula

In the last section, we learned to create a simple formula. Let's continue with constructing formulas.

Now let's say we wanted to create a weekly expense report in Excel.

We stopped for gas twice on Tuesday and have two receipts, one for \$29.92 and the other for \$32.51. We'd click on the correct cell (in this case D4) and type =29.92+32.51. Remember, a formula always begins with an equal (=) sign.

The equation appears in the formula bar as well as in the individual cell as you type it. Hit Enter and the equation disappears, leaving only the sum of the two numbers:

If you want to see the formula again, click on the cell. The formula will be visible in the formula bar.

You can also perform a calculation based on the values within a series of cells. Let's add up the figures in column D. The individual cell coordinates are D4, D5, and D6. We want the total to appear in cell D8, in the row called Daily Total. Select that cell (D8) and verify your selection by checking the Name Box.

Now we'll type =SUM(D4:D6) in Cell D8. The equals sign tells Excel that you are going to enter a formula. The SUM tells it to add the following numbers and the colon tells it the range (D4 to D6).

You'll notice the cell references appear in blue.   Also, the cells that you reference become shaded blue as well. This gives you a visual representation of the selected range.

Hit Enter and the sum will appear in cell D8.

Because the sum depends upon the numbers in cells D4 thru D6, Excel will automatically recalculate the sum whenever the values of those cells change.

Order of Operation

When constructing a formula, it's important to remember that Excel reads formulas from left to right using the natural order of arithmetic operation. In other words, Excel multiplies and divides before it adds and subtracts.

If you want it to add or subtract before it multiplies and divides, or you want two numbers multiplied first, then you must use parentheses. Look at the example below:

In this formula, Excel will first add 5+2 since they're in parentheses. Next, it will multiply that answer times 63. Finally, it will add five.

This is the correct order. If you wouldn't have added the parentheses, the answer would have been 136. Here's why. Excel would have multiplied 2*63, then added five, then added five more. Make sure that you "talk" to Excel by adding parentheses when needed.

Editing a Formula

You can edit a formula by clicking on the cell and editing the formula in the Formula Bar.

Here's our formula as shown in the formula bar:

We changed 63 to 65 in the Formula Bar, then hit Enter.

The calculation is automatically changed in the cell:

## Formula Errors

Sometimes when you type a formula in, instead of Excel calculating and providing you with the answer, you'll get an error message such as #NAME?

When this happens, Excel is letting you know that some element of the formula or the cell in the formula is stopping Excel from doing its calculation. These types of error messages are called error values.

When you see an error value appear, you must figure out what caused it, then edit the formula to fix the problem.

 Error Message What It Means #DIV/0! This is the error value shown when a formula requires division by a cell that contains the value 0 or is empty. #NAME? This appears when a formula refers to a range name that doesn't exist. (D15:D22) It can also appear when you don't put quotation marks around text used in a formula because Excel will think the text is a range name. #NULL! This appears when you insert a space instead of a comma to separate cell references that are used for arguments in functions. #NUM! This appears if Excel finds a problem with a number in formula, such as a wrong argument in a function or calculation that creates a number too large or small to be represented. #REF! This appears when Excel finds an invalid cell reference. If you delete a cell that's referenced in a formula, you might see this message #VALUE! This appears when you enter in the wrong type of argument or operator in a function. It can also appear when you enter a mathematical operation for a cell that contains text.