## [Solved] Rounding a number to '9'

### [Solved] Rounding a number to '9'

I was given this rounding formula to help me round currency for my business to '.99' and this works great.

=ROUND(K2+0.1;0)-0.01

But I have 2 countries that don't break the currency down in the same way and ideally I need them to end with either a 5 or a 9.

For example:

Price is converted to 1313.42 from GBP

it then needs to become 1319

Is there a formula or can someone help me develop a formula to achieve this?
Last edited by Nath@homesmart on Wed Aug 14, 2019 2:42 pm, edited 1 time in total.
OpenOffice 4.1.3
Nath@homesmart

Posts: 8
Joined: Tue Mar 05, 2019 12:47 pm

### Re: Rounding a number to '9'

Nath@homesmart wrote:... either a 5 or a 9.
For example:
Price is converted to 1313.42 from GBP
it then needs to become 1319

So, why does it become 1319 in this case and not 1315?

More detail (better explanation or more examples) is needed to solve this.

keme
Volunteer

Posts: 3208
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: Rounding a number to '9'

So I am figuring prices for Japan and Mexico ad their currency doesn't break down the same way are GBP, USD or Euro's.

And to make everything a rounded price we have those currencies rounded up or down to the nearest .99 using the formula:

=ROUND(CELL+0.1;0)-0.01

For example:

If our converter changes £20.99 to \$50.72 it would be rounded up to \$50.99

If our converter changes £19.99 to \$49.43 it would be rounded up to \$49.99

So in the case of Japan and Mexico it doesn't work in the same way and can't round it to .99.

£20.99 would give me ¥5,583.01 and would be rounded to ¥5582.99

SO...

I want a way of rounding the converted figure to the nearest 9 not nearest .99

£20.99 would give me ¥5,583.01 and we want it to be rounded to ¥5,579

OR

if the conversion was ¥5,586 to be round to ¥5,589.

Hope this gives a little more clarify.
OpenOffice 4.1.3
Nath@homesmart

Posts: 8
Joined: Tue Mar 05, 2019 12:47 pm

### Re: Rounding a number to '9'

Since the rounding operates on the decimal digits, you've to change the unit into a decimal digit and then round, then make it a unit again. Division then multiplication by 10 will do the trick:
=ROUND(CELL/10+1;0)*10-1

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the *EDIT button if your issue has been fixed.
AOO 4.1.6 on Xubuntu 19.04 and 4.1.5 on Windows 7 (with winPenPack port).

Off-line until Aug. 31.

Hagar Delest
Moderator

Posts: 28529
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

### Re: Rounding a number to '9'

Thank you so much.

That is extremely useful and will help a lot.

Kindest regards

Nathan
OpenOffice 4.1.3
Nath@homesmart

Posts: 8
Joined: Tue Mar 05, 2019 12:47 pm