round to highest 1000

[DarS]

I have a query with a calculation that I would like to show rounded to the
next highest 1000. Any suggestions?

 

[Klatuu]

Assume x = 14327
x mod 1000 returns 327 (the remainder of dividing x by 1000)
subtract that from x
add 1000
This is the formula
= x - (x mod 1000) +1000

It returns 15000

 

[Robert Morley]

Klatuu's way works well provided that you're using integers. If you're
using floating point, however, you should use something like the following
instead:

-int(-x / 1000) * 1000

As to why the double-negative, instead of simply int(x / 1000) * 1000, try
it on an even multiple of 1000 and you will understand.


Rob

 

[Klatuu]

Good catch, Robert. Your's is more elegant than mine.
But, I tried your formula with the - signs removed on an even 14000 and it
returned 14000. Did I misunderstand what you were saying?

 

[Marshall Barton]

Assume x = 14327
x mod 1000 returns 327 (the remainder of dividing x by 1000)
subtract that from x
add 1000
This is the formula
= x - (x mod 1000) +1000

It returns 15000

Dave, that will "round" 14000 to 15000

Would this be better?

= (x \ 1000 - ((x mod 1000) > 0)) * 1000

 

[DarS]

That is a thing of beauty. Works great ! Thank you!

 

[Allen Browne]

Try:
-1000 * Int( [Amount] / -1000)
replacing [Amount] with your field or expression.

Explanation:
http://allenbrowne.com/round.html#RoundNegativePlaces

 

[Marshall Barton]

That is a thing of beauty. Works great ! Thank you!

You're welcome, but I like Robert's expression better.

 

[Robert Morley]

Sorry, I screwed up in my explanation. What I said was not at all what I
meant! I'm tired!

With the minus signs removed, you're rounding down; with the minus signs
inserted, you're rounding up. Both formulae work fine with an even 14000;
it's a question of which direction you want to go when it's NOT an even
multiple of 1000.


Rob

 

[Robert Morley]

Hey, that's my formula (give or take putting the negative on a different
term). :Þ


Rob

 

[John W. Vinson]

Hey, that's my formula (give or take putting the negative on a different
term). :Þ

Now, now, gentlemen... let's not cause a negative atmosphere here! <bg>

John W. Vinson [MVP]

 

[Stuart McCall]

Hey, that's my formula (give or take putting the negative on a different

Now, now, gentlemen... let's not cause a negative atmosphere here! <bg>

John W. Vinson [MVP]

Groan! You get worse John I swear.

atmosphere = Abs(attitude)