Microsoft Office Tutorials and References

In Depth Information

**Converting the currency**

Figure 5.6

When copying formulae goes wrong

If you’re racing ahead, you might already have discovered that the technique you

used to copy formulae for the total euro cost doesn’t work when you try it for the

total pound cost. At best, it gives us some extremely optimistic calculations. I

wouldn’t say no to half a week’s petrol for 73p and two concert tickets for 26p. At

worst, it gives us complete nonsense, with error messages in the cells, as you can

see in Figure 5.7.

The error messages are probably a better result than the numbers, because at least

it’s obvious there’s something wrong with those cells. There’s a risk that

calculation errors might be missed when they are more subtle, which is why it’s important

to understand what’s gone wrong here.

In Figure 5.7, I’ve edited the formula for the last total pound formula so that you

can see the cells it’s based on highlighted in blue and green. As Excel has copied

the formula down the spreadsheet, it has updated the row references in it. The

formula in cell F10 is now:

=E10/B7

The problem is that we didn’t want Excel to update the row for both of the cell

references; we only wanted it to update the row for one of them – the total euro

cost, in column E. We wanted it to use the same exchange rate cell in column B