[Solved] Rounding a number to '9'

[Nath@homesmart]

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?

[keme]

... 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.

[Nath@homesmart]

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.

[Hagar Delest]

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.

[Nath@homesmart]

Thank you so much.

That is extremely useful and will help a lot.

Kindest regards

Nathan