Free computer Tutorials
|HOME||Stay at Home and Learn|
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.
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:
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:
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.
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.
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:
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:
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:
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: