D
DarS
I have a query with a calculation that I would like to show rounded to the
next highest 1000. Any suggestions?
next highest 1000. Any suggestions?
K
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
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
R
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
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
K
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?
But, I tried your formula with the - signs removed on an even 14000 and it
returned 14000. Did I misunderstand what you were saying?
M
Marshall Barton
Klatuu said:
Dave, that will "round" 14000 to 15000
Would this be better?
= (x \ 1000 - ((x mod 1000) > 0)) * 1000
D
DarS
That is a thing of beauty. Works great ! Thank you!
A
Allen Browne
Try:
-1000 * Int( [Amount] / -1000)
replacing [Amount] with your field or expression.
Explanation:
http://allenbrowne.com/round.html#RoundNegativePlaces
-1000 * Int( [Amount] / -1000)
replacing [Amount] with your field or expression.
Explanation:
http://allenbrowne.com/round.html#RoundNegativePlaces
M
Marshall Barton
DarS said:
You're welcome, but I like Robert's expression better.
R
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
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
R
Robert Morley
Hey, that's my formula (give or take putting the negative on a different
term). :Þ
Rob
term). :Þ
Rob
J
John W. Vinson
Now, now, gentlemen... let's not cause a negative atmosphere here! <bg>
John W. Vinson [MVP]
S
Stuart McCall
Hey, that's my formula (give or take putting the negative on a different
Groan! You get worse John I swear.
atmosphere = Abs(attitude)
Groan! You get worse John I swear.
atmosphere = Abs(attitude)