Rounding currency numbers

[svenvaeth]

Hi there, I got a problem with rounding numbers. I have a table with different numbers:

<style type="text/css"><!-- br {mso-data-placement:same-cell;} --></style>

€ 0.21
€ 0.58
€ 1.22
€ 1.78
€ 9.99
€ 10.00
€ 10.10
€ 10.60
€ 10.80
€ 11.00
€ 11.55
€ 12.12
€ 14.99
€ 15.00
€ 15.55
€ 18.18
€ 19.99
€ 20.00
€ 20.10
€ 24.99
€ 25.00
€ 99.99
€ 101.55
€ 149.99
€ 150.00

<colgroup><col style="width: 97px"></colgroup><tbody>
</tbody>

I want to get a number like xxx.59 EUR out of it, i.e. the first cell 0.21 should give 0.59, third cell 1.22 -> 1.59, the cell with 18.18 should give 18.59. So always a 59 behind the decimal point. I found several formulas to round to any significant digits. The problem is, it should be only ONE formula that I use. Most of the formulas differentiate between the digits, e.g. 1.18, 18.18 and 118.18. The result would be different and not .59

Does anybody have an idea how to solve that issue? I hope you can understand what I wanna do

Thank you for your help.

Sven

 

[thisoldman]

I think this is what you want.

Original	New
$1.21	$1.59
$12.43	$12.59
$0.99	$0.59

<tbody>
</tbody>

Formula in New column

=INT(Original) + 0.59
​

 

[energman58]

Depends what you are trying to do:

If you want to convert the number to one that ends in .59 you can use a formula like this:

=round(A1,0)+0.59

This rounds first and adds 0.59 so 22.5 becomes 23.59 and 22.1 becomes 22.59

If you want to change 22.5 and 22.1 and 22.8 to 22.59 then the formula is:

=Rounddown(A1,0)+0.59 or =INT(A1)+0.59

If you simply want to DISPLAY the number as XX.59 then you can use a custom format to do this:

Select the cell
Open the format cells dialog box at the Number tab
Select custom

then insert $#,##0.59 in the "Type" box

However this rounds before displaying (and the number itself isn't changed) so 21.5 displays as 22.59 just like the first formula and a formula refereing to this will still use 21.5 in its calculation.

Any help?

 

[svenvaeth]

Thank you guys, the INT works, but the format is even better, with €#,##0.59 I get exactly what I want.

Thank you very much.