Excel Formulas - The Basic Operators

In this section, we'll discuss formulas some more. Because formulas are at the heart of spreadsheets. The reason you're using a spreadsheet is to work out some sort of mathematical calculation, whether that be a family budget calculation or a company profit calculation. And you can't do those calculations without using formulas.

Adding Up

You've seen how to add up in Excel. You either used individual cells to add up, like this:

= A1 + A2 + A3 + A4

Or you have used a range of cells with the Sum function. Like this:

= Sum(A1:A4)

Either way you get the same answer. But you can combine the two to add up. For example, if you wanted to add up cells A1 to A4 and cell A10, you'd do it like this:

= Sum(A1:A4) + A10

You can also use the Sum function alone. Like this:

= Sum(A1:A4, A10)

Here we have entered a range of cells in the brackets - A1:A4. But after that, we added a comma then the final cell we wanted to add up. If we also wanted to add cells A12 and A14 to our sum, we just add a comma then the cell reference. Like this:

= Sum(A1:A4, A10, A12, A14)

Try using this Sum function yourself by doing these exercises:

Exercise 1

On a new spreadsheet, enter the number 3 in the following cells: A1, B1, C1, D1. Enter the number 3 in the cell A2. Use only the Sum function to add up the values in the cells. Display the answer in cell A4. In other words, the spreadsheet should look like the one below:

In the picture above, cell A4 displays the answer. Only a Sum function was used.

Exercise 2

Enter the number 3 in the following cells: A1, B1, C1, D1, E1. Enter the number 3 in the following cells: A3, C3, E3. Add them all up using a Sum function. Display the answer in cell A5. The spreadsheet will look like this:

The correct answer, 24, is displayed in cell A5. Only one Sum function was used.

 

Multiplying

You saw how to multiply two numbers together. Just use the asterisk symbol with the cell references. Like this:

= A1 * A2

But what if you wanted to multiply a long range of values together? You might want to multiply all the numbers in the cells A1 to A10. Surely you don't have do this = A1 * A2 * A3 * A4 * A5, etc?

You'd be right - you don't have to enter all the cell references. You can do the same as in the Sum Function, just use A1:A5. But instead of using the word Sum you use the word Product. Like this:

=Product(A1:A5)

If you look back at Exercise 2, the number 3 was in the cells A1 to E1. We then added them up. If you wanted to multiply them all instead, use Product. To further illustrate what Product does, this is what we wanted to multiply:

= 3 * 3 * 3 * 3 * 3

So 3 times 3 = 9. Multiply the 9 by the third 3 to give 27. Multiply the 27 by the fourth 3 to give 81. Multiply the 81 by the fifth 3 to give 243. And that's what Product does: Multiplies a range of cells together. Just like the Sum function, you can add other cells after a comma. Like this:

= Product(A1:E1, A3)

In the above function, our answer of 243 will be multiplied by whatever is in cell A3.

Try using Product yourself with these exercises:

Exercise 3

In the cells A1 to E1 enter the following values: 1, 2, 3, 4, 5. Use Product to multiply them all together. Display your answer in cell A3. So your spreadsheet will look like this:

Exercise 4

In the cells A1 to E1 enter the following values: 1, 2, 3, 4, 5. In the cells A3, C3 and E3 enter the following values: 6, 7, 8. Use Product to multiply all the values together. Display the answer in cell A5. Your spreadsheet will look like this one:

Subtraction

To subtract one value from another, you just use the minus sign in between your cell references. Like this:

= A1 - A2

Below is an image from a spreadsheet showing a subtraction formula:

Cell A3 is where the answer is displayed, and where we entered the formula.

If you want to subtract more than two cells you can do it like this:

= A1 - B1 - C1

Subtraction is fairly straightforward in spreadsheets, and shouldn't cause you too many problems.

Division

If you want to divide one number by another the symbol to use is this one:

/

That's the forward slash, and can be found just to the right of the full stop on your keyboard. You use it like this:

= A1 / C1

There are a number of times when you would want to divide, but wouldn't need the divider symbol at all. For example, you use division to calculate Averages, but you wouldn't need the divider symbol here because Excel has a separate Average function. Likewise, if you wanted to calculate monthly payments, you wouldn't need to use the divider symbol because Excel had quite a number of financial functions you can use. (We'll meet some of these later in the course.)

In the next part, we'll take a look at how to combine these basic Math operators in Excel.