Microsoft Office Tutorials and References

In Depth Information

**Converting the currency**

for each of the formulae. Instead, it has moved the cell reference for the exchange

rate down one row each time it copied the formula into a new row, similar to what

it did for the total euro cost. By the time we get to cell F10, we’re dividing the total

euro cost for concert tickets taken from cell E10 (correct) by the price of the

treehouse hotel in cell B7 (utter nonsense).

No wonder there are error messages. The ‘DIV/0!’ error message has happened

because Excel is trying to divide the total hotel bill by the value of an empty cell

underneath the exchange rate, and you can’t divide by zero. The ‘VALUE!’ error is

what happens when you divide a number by the word ‘Price’. These are two of the

rare instances where you’ll see an exclamation mark used in a computer error

message, so you can tell how alarmed Excel is about this.

The solution is to put a special character into the cell reference in our formula to

tell Excel that we don’t want the row to be modiﬁ ed when the formula is copied.

That special character is the dollar sign and it goes before the row reference. The

formula for the total pounds cost on row 5, then, should be:

=E5 / B$2

Now, when you copy the formula into the cells below, the reference to E5 will be

changed for each row, but the reference to cell B2 will stay the same.

Autosum

Figure 5.7