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.