Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Standard User GeoffB
(fountain of knowledge) Sat 07-Apr-12 11:04:55
Print Post

Excel Currency format


[link to this post]
 
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.

Dell Studio 15 with Win7 64bit and 4GB RAM
IE9 and Live Mail
BT Infinity via HH3
Anonymous
(Unregistered)Sat 07-Apr-12 11:36:55
Print Post

Re: Excel Currency format


[re: GeoffB] [link to this post]
 
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-...
Standard User cheshire_man
(knowledge is power) Sat 07-Apr-12 14:02:40
Print Post

Re: Excel Currency format


[re: GeoffB] [link to this post]
 
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.

Tony


Register (or login) on our website and you will not see this ad.

Standard User Mikey2
(eat-sleep-adslguide) Tue 10-Apr-12 21:03:41
Print Post

Re: Excel Currency format


[re: GeoffB] [link to this post]
 
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.

Mike
If you have to swallow a frog, try not to think about it. If you have to swallow two frogs, don't swallow the smaller one first.
  Print Thread

Jump to