Round function and Trunc function

[bevc]

Could somebody help me please, I'm having a slight problem with a formula. I have this nested inside an 'if/iserror':

("£"&ROUND(G33/G77,2)&...

to give a currency amount, and it works okay, except that it cuts the zero off the end of the amount if the second figure after the decimal place is a zero, eg it gives '£30.4' when I want '£30.40'. I tried the 'trunc' function too but that didn't work. Is there a function or a way to specify EXACTLY how many digits you want after the decimal place, ie a currency amount?

Thanks very much.

[BadlySpelledBuoy]

Increase the cell format to 2 decimal places.
If you right click the cell you'll get an icon for "increase decimal places".

BSB

[sandy666]

you can try with: ("£"&FIXED(G33/G77)&...

[Pete_UK]

Note that your formula will not return a currency amount - it will return a text value that looks like currency. It would be better to format the cell as currency (which will give you the £ sign automatically) and allow the formula to return a number. If you really want to return a text value, then you will need to do it like this:

("£"&TEXT(ROUND(G33/G77,2),"0.00")&...

which will give you two decimal places.

Hope this helps.

Pete

[sandy666]

Pete, this is exactly the same:

("£"&TEXT(ROUND(G33/G77,2),"0.00")&...

("£"&FIXED(G33/G77)&...

FIXED() with no parameters returning value as text with 2 places after dot.
Both are OK but second is shorter

[Pete_UK]

Yes, I thought the OP might need a bit more in the way of explanation.

Pete

[sandy666]

No problem, it was my humble opinion only