[Solved] Rounding a number to '9'

Discuss the spreadsheet application

[Solved] Rounding a number to '9'

Postby Nath@homesmart » Wed Aug 14, 2019 11:06 am

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'

Postby keme » Wed Aug 14, 2019 1:19 pm

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.
User avatar
keme
Volunteer
 
Posts: 3208
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Rounding a number to '9'

Postby Nath@homesmart » Wed Aug 14, 2019 2:11 pm

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'

Postby Hagar Delest » Wed Aug 14, 2019 2:36 pm

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.
User avatar
Hagar Delest
Moderator
 
Posts: 28529
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Rounding a number to '9'

Postby Nath@homesmart » Wed Aug 14, 2019 2:43 pm

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 11 guests