Using Formulas
We use formulae to perform a variety of operations, not only calculations. Some of these are special formulae that we call Functions. You will meet some of these as you develop your Excel skills at a higher level.
In this section we shall look at some straightforward formulae for adding, subtracting, multiplying and dividing.
As you have seen when calculating the SUM of data in a number of cells, we place a formula in a cell in the same way as text or numbers. It can be a very simple formula, such as adding the contents of 2 or more cells.
First of all, we need some data, so:
- Go to FILE and NEW
- Type in the data as shown here:
Format the spreadsheet as follows:
- The spreadsheet font is 10-point Arial
- Change the title in cell A1 to 12-point Arial Bold and centre it across the columns A to G
- Change the word TOTAL in cell A11 to Bold
- Change all the headings in row 3 to 10-point Arial Bold and centre them in their cells
- Change the data in columns B and C (B5:C9) to 10-point Arial Bold
- Insert a blank column between columns C and D
- Adjust the column widths if necessary
The formatted sheet should look like this:
In this spreadsheet we shall write FORMULAE for four types of calculation (addition, subtraction, multiplication and division), using the two sets of numbers in columns B and C.
Addition
The first step is to add together the two numbers in cells B5 and C5 (that is, B5 plus C5) and put the answer in cell E5. To do this:
- Click in cell E5 and type an = sign.
The = sign is your way of telling the computer that what follows in this cell is some kind of calculation. Without the = sign, you are only inputting text or numbers, and the program will not recognise that you want to calculate something.
- Complete the formula in cell E5 so that it looks like this: =B5+C5
- Confirm this formula by pressing the ENTER key
- Click in cell E5 again to see the result of the formula. It should be 14.
What has happened to the formula? It is still there but in cell E5 you can see only the result of the calculation. To see the formula, look up at the Formula Bar (below the Formatting toolbar).
The next step is to add the other pairs of numbers in rows 6 to 9, using a similar formula. You could make a separate formula for each row but - as you saw in section 3.2 - the quicker and better way is to replicate the formula in E5 into the cells E6:E9 using the Fill Handle. Refer back to section 3.2 to remind yourself how to do it.
Subtraction
Column F requires a different formula. This time we must create a formula in cell F5 to calculate the number in cell B5 minus the number in cell C5.
- In cell F5 type: =B5-C5
- Use the fill handle in F5 to replicate the formula into cells F6 to F9
Multiplication
Create a formula in cell G5 to multiply the two numbers in cells B5 and C5.
Note: In Excel we use the symbol * (asterisk) for Multiply.
- In cell G5 type: =B5*C5
- Replicate the formula into cells G6 to G9
Division
Create a formula in cell H5 to divide the number in cell B5 by the number in cell C5.
Note: In Excel we use the symbol / (forward slash) for Divide.
- In cell H5 type: =B5/C5
- Replicate the formula into cells H6 to H9
At this point your spreadsheet should look just like this one:
Calculating Totals Using the SUM function
Look back at sections 3.1 to 3.3 to remind yourself how to use the SUM function. Then complete your spreadsheet by doing the following:
- Use Autosum in cell E11 to calculate the total for cells E5:E9
- Replicate this formula into cells F11:H11
- Format all the numbers in column H to 2 decimal places
Here is the finished spreadsheet:
- Save the spreadsheet with the name FORMULAE.XLS.
- Print a copy of the spreadsheet and show it to your tutor
- Go to FILE and CLOSE
Next: more new practice and advanced formulas.
