round currancy, but show .00 (1 Viewer)

[jomuir]

I have a field of currancy (£) that is set to 0 decimal places and this is giving me the right values. However I want tese values to be displayed as proper currancy i.e. £xxxx.xx - so I presume I want my figures rounded to pounds, and then to show zero pence.


So I have
£103,525£78,679£103,525£103,525£93,586£93,586

Like to have
£103,525.00£78,679.00£103,525.00£103,525.00£93,586.00£93,586.00

However the field is calculated by a formula, so the really values actually have lots of decimal places:-
£103,524.701726131£78,678.673927748£103,524.701726131£103,524.701726131£93,586.290606778£93,586.290606778

 

[Brianwarnock]

Do the rounding in the formula =round(calc,0), but set the cells for normal 2 decimal currency.

Brian

 

[qafself]

Yes, set the currency to two decimal places and then use round(cell ref,0) to get rid of pence.

Note - using round means that you are changing the value stored, but when the currency is set to zero decimal places, the value stored is the calculated value not the displayed value.

If you are comparing totals for checking, using round. will lead to anomalies.

Another solution would be to use a custom format.