Writing a Formula
To create a formula you need some data to work with. For example the screenshot below shows a number of birds semi-annual migration route. The distance is already shown in KM, but if we want it in Miles then we can use a formula to work it out. 1 KM is 0.621 Miles; therefore we need to multiply the number in cell F2 by 0.621. To write formulas for this sum choose the cell you want the answer to be shown in (in this case it's H2) then start with an equals sign, and then define the values of the other entries. In this case we will use cell F2 * (for multiply) and 0.621. The formula will then look like this: =F2*0.621 as you can see in screenshot 1 this formula has been entered, and in screenshot 2 it shows the outcome:
In screenshot 2 you can see the formula bar shows cell H2 with the formula we wrote.
Repeating a Formula:
To find out the rest of the distances for the other birds you can repeat the formula created for the first one instead of re-writing it. There's 2 ways to do this,either select the cell you want to repeat, then copy (Ctrl+C or right click copy) and paste it into the new cell, in this case it would be H3. The other way is to select the cell you want to repeat then in the bottom right hand corner of the highlighted cell there should be a small box, called its fill handle, click and drag that down the column, this will apply the same formula to each cell in the column. Screenshot 3 and 4 shows this:
Transforming Formulas: Relative versus Absolute:
Spreadsheets use two types of cell references, relative and absolute. A relative cell allows the software to change the reference when filling and pasting, whereas an absolute cell tells the software never to change the reference. The example above is using relative cell reference because F2 is used. Therefore this allows the formula to be changed automatically when auto-filled; screenshot 5 shows the formulas. The absolute form for this cell would be $F$2.
Spreadsheets use two dimensions, columns and rows, therefore a formula can be relative in 2 ways, and this makes 4 cases:
- F2 - column and row are relative.
- $F2 - column absolute, row relative.
- F$2 - column relative, row absolute.
- $F$2 - column and row are absolute.
Cell Formats
When working with longer numbers in excel it can be difficult to read, so you can format the data to show the numbers you only need, i.e. showing whole numbers. Screenshot 6 shows our new column with long numbers that we want to change so it shows whole numbers:
First select the cells you want to format, right click, and then select Format Cells. Screenshot 7 shows the window that appears.
As you can see a number of categories are available to format. We need to select number, and then change decimal places to 0 because we just want the whole number.
Once this is completed, click OK. Screenshot 8 shows the result.
