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:
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
Search JabSto ::

Custom Search