Microsoft Office Tutorials and References

In Depth Information

**Calculating the quantities**

When you enter that, you see the value 5 go into the cell, which is half our trip

length. So what about our petrol budget? This is also calculated per day. If you

thought the right answer was to use the same formula as the one used for car hire,

award yourself half a point. For the full point, you need to take a different approach.

The size of the petrol budget actually depends on the number of car hire days

more than it does the length of our holiday, so you should base the calculation for

the petrol budget on the number of car hire days. In cell D8, which represents the

number of days of petrol you need to budget for, enter the formula:

=D7

This takes the number of car hire days from cell D7 and puts it into the cell for the

number of days a petrol budget will be needed. The numbers on screen are the

same as they would have been if we’d reused the same formula, so why make a

distinction? It’s because it makes the spreadsheet more ﬂ exible. What if, later on,

we decide to rent a car for the whole holiday? By doing the formula this way,

we only need to change the number of car hire days and our petrol budget will

update automatically. Similarly, we can take out all car related costs by setting

the car hire days to zero. If we had calculated our petrol from the number of

days, we would have to update that formula too, whenever we changed our car

hire formula.

The art of creating good spreadsheets is partly about working out relationships like

this. It’s not always easy, but the more speciﬁ c you can be about describing how

numbers relate to each other, the more reliable your spreadsheet will be. I’ve only

got six rows on my spreadsheet, but your full holiday budget might run to 20 rows

or more. If you need to change two things to reﬂ ect one decision, there’s always a

possibility you might introduce inconsistencies or forget to update something,

which will throw your sums out.

You can also use the mouse or cursor keys to indicate cells you want to use

in a formula. Enter the equals sign into a cell then either click on another cell

or use the cursor keys to move around to the cell you want. Press Enter and

its reference will come up in the formula.