Round up or down to specific number

[Ms. Latte]

When I do the price, there are two step involve
1. I have to convert USD to Thai currency by multiplying with specific
number.
2. When i got the Thai currency (which may have demicle) , I have to round
up or down to these number i.e. it must end with 500, 700 or 900.

0-299 round down to 900 ex: 3250 round to 2900
300-599 round to 500 ex: 4365 round to 4500 if 5580 round to 5500
600-700 round to 700 ex: 8600 round to 8700
799-900 round to 900 ex: 4800 round to 4900
901-000 round to 900 ex: 3980 round to 3900

Your help is valuable as I must do this thousand time.
Thank you very much

 

[Billy Liddel]

It might help if you gave us the conversion rate that you are using?


Peter Atherton

 

[Ms. Latte]

The conversion rate is vary depending on the time. But i will key in
conversion rate in one cell and use that cell in multiplying. Is there a way
that I can combine this cell into the formular? Thank you for your kind help.

 

[Ron Rosenfeld]

When I do the price, there are two step involve
1. I have to convert USD to Thai currency by multiplying with specific
number.
2. When i got the Thai currency (which may have demicle) , I have to round
up or down to these number i.e. it must end with 500, 700 or 900.

0-299 round down to 900 ex: 3250 round to 2900
300-599 round to 500 ex: 4365 round to 4500 if 5580 round to 5500
600-700 round to 700 ex: 8600 round to 8700
799-900 round to 900 ex: 4800 round to 4900
901-000 round to 900 ex: 3980 round to 3900

Your help is valuable as I must do this thousand time.
Thank you very much

If I understand what you are writing, I make the following assumptions:

1. Thai currency values of less than 300 baht? will, according to your scheme,
round to -100. If you have something else in mind, you'll need to explain it
more clearly.

2. You have left undefined how to handle values between 701-799. I have
assumed that is a typo and assumed that 600-799 will round to 700.

Given that, I think the following will work.

A3: Thailand Baht (computed using the conversion rate, or however)

Enter the following table:

F1: 0 G1: -100
F2: 300 G2: 500
F3: 600 G3: 700
F4: 800 G4: 900

Use this formula to round:

=INT(A3/1000)*1000+VLOOKUP(MOD(A3,1000),$F$1:$G$4,2)

This formula will work on positive numbers only.

If you might also be dealing with negative numbers, then try:

=(INT(ABS(A3)/1000)*1000+VLOOKUP(MOD(
ABS(A3),1000),$F$1:$G$4,2))*SIGN(A3)
--ron

 

[Ms. Latte]

Mr. Joel has answered me this following message which is another good
alternative. Thank you everyone in sharing your knowledge.
Thanks.

From Joel
You need to round the number down to the nearest 1000. Then look up get the
value between 0 to 999 and perform a lookup using the lowest number of your
range. The lookup value is then added/subtract to the rounddown value.


=ROUNDDOWN(D4,-3)+LOOKUP(MOD(D4,1000),{0,300,600,799;-100,500,700,900})