# Thread: rounding equation

1. ## rounding equation

hey guys,

i feel really incompetent here. i cant come up with a solution that rounds any number UP to the nearest .25.

Right now, let num be my number (a currency value) that I want to round up to the nearest quarter to yield result...no matter how much closer it is to the lower quarter. For example, 1.80 needs to be 2.00, not 1.75.

Right now, my equation rounds up & down

num/25 = mid
truncate mid to only two decimal places
mid*25 = result

does anyone have a good idea how to tweak this to always round up?
im thinking I need to add something to num or maybe multiply by 100 first to make it an integer, and then divide later to get it back to decimals...but nothing is working out for me.

2. Try

1) Multiply by 4 <== Where did that come from?

2) Add just short of your machine value of 1. I used 0.9999999999. It did NOT work when I used 0.999999999999999. It may take some experimentation to find the right value.

3) Truncate to integer

4) Divide by 4.

3. Originally Posted by TKHunny
Try

1) Multiply by 4 <== Where did that come from?

2) Add just short of your machine value of 1. I used 0.9999999999. It did NOT work when I used 0.999999999999999. It may take some experimentation to find the right value.

3) Truncate to integer

4) Divide by 4.

Worked for me beautifully. Thank you very much!

4. What value of "almost 1" did you use? ...and in what environment?

You were really close with the "/25" and then "*25" version. Notice that 1/(0.25) = 1*4. Look familiar? You just had the scale off a bit.

5. Originally Posted by TKHunny
What value of "almost 1" did you use? ...and in what environment?
I took it out to 10 decimal places (probably didn't need so much precision)

0.9999999999

I used this to do an update in a SQL server database. Had an issue where prices of something needed to be rounded UP to the nearest quarter. T-SQL has a ROUND function where you can specify to how many digits. My formula looked like this when I was done:

(ROUND ((field*4)+.9999999999, 0)) / 4

It worked beautifully. Just embed that in a UPDATE statement and watch it fly. I'm using a Windows box.

I remembered an old trick you can do to round...almost worked for what I was after: pick your interval (my case 25), multiply by the reciprocal (why I divided by 25), truncate to two decimal places, and multiply the interval back in.