Excel Currency format

I am currently helping a friend to do some accounts for his elderly mother's investments at the end of the tax year. She has about a dozen unit trusts inherited from her husband and they were all valued at strange prices on April 5th (i.e. one was 34.373p per unit). When I get to the end of the totals column (£1634.56) and add this to the other assets (£23874.34) I get the pence column adding up to 91p - even I can add up that 56 and 34 are 90, so I assume that the fractional units in the investments column are being adjusted somehow. Of course, I can change this manually, but I can't find anywhere how to force a spreadsheet to keep currency format and add two subtotals as they appear, rather than re-calculating the cells.

It looks as format on the number of decimal places is the culprit.

Hope these two links can help;

http://www.dummies.com/how-to/content/how-to-display...

http://www.addictivetips.com/microsoft-office/excel-...

I think the problem is that the displayed row results, value x quantity, is being rounded for display but not for summation.

I suggest you envelop the 'value x quantity formula inside rounding function, i.e. round(value*quantity,2). This seems to produce in the correct result.

As you state Excel is adding up the actual figures, to show the totals as displayed, you need to set the workbook to do this.

On Excel 2010, go to File - Options - Advanced and scroll down to "When calculating this workbook" and click on the "set precision as displayed" and OK out.