I have an Excel sheet that calculates Flat Rate VAT (at 9%) on monthly gross
sales, and then deducts that VAT amount and any company expenditure for that
month from the gross sales amount to return the balance.

All the cells are formatted as (UKP) currency to 2 decimal places.

For example:

Cell A1 shows a gross sales value of £5,522.50

Cell A2 shows a VAT value of £497.03  (calculated as 9% of the value in Cell
A1)

Cell A3 shows an expenditure value of £350.20

Cell A4 shows the balance  =A1-A2-A3

The result in Cell A4 should be £4,675.27, but the result I get is
£4,675.28.  I conclude this is because the true VAT value in Cell A2 is
£497.025, which Excel displays as rounded up to £497.03 for 2 decimal
places.

Unfortunately, when Excel does the calculation for Cell A4, it uses the Cell
A2 true VAT value of £497.025.  This gives the result of £4,675.275, which
is then rounded up to £4,675.28, giving the 1p error.

What is the best way to prevent this from happening?  Can I force the
formula in Cell A4 to use the displayed figure in Cell A2, or is there a
more elegant way of resolving this problem?