# How to zeroe the last 3 digits of a number (not rounding)

In LibreOffice Calc, how do I zeroe the last 3 digits of a number

e.g.

123,456,789


gives

123,456,000


Note that numbers are integers. There is no decimal part.

edit retag close merge delete

Sort by » oldest newest most voted

=ROUNDDOWN(A1;-3)

more

@Mike Kaganski: Don't you feel the behaviour of ROUNDDOWN() applied to negative numbers to be blowzy? It works upwards then using the common way to speak in mathematics, where it should be mandatory that (num) and (something + num) should go both "up" or both "down" ifr (something) varies.
[Yes. I know the specification of ROUNDDOWN(). It tends to enforce the traditional confusion in speaking about negative numbers. ROUNDTOWATDSZERO() would be a bit longer but much better.]

( 2018-03-12 10:32:00 +0200 )edit

Well - terminologically speaking, yes :)

( 2018-03-12 10:36:59 +0200 )edit
1

Yes. I can suggest a formula avoiding the above mentioned issue, and actuaölly preferred by myself:;
=SIGN(A1)*INT(ABS(A1)/1000)*1000 and here you may even replace the second part with a ROUNDDOW(ABS(A1);-3)).
Yes, It's longer. But better is better than shorter. (?)

( 2018-03-12 10:39:20 +0200 )edit

=SIGN(A1)*FLOOR(ABS(A1);1000)

where A1 is the cell where you stored your number.

FLOOR() rounds down to the nearest multiple of the second argument. Consequently, you're not limited to powers of 10, e.g. you can choose 125 to round down to a 125-quantum.

If this answer helped you, please accept it by clicking the check mark ✔ to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

(edited to address @Mike Kaganski's remark)

more

This will not work for negatives; and even if you change the sign of the 1000 (like "SIGN(A1)*1000"), it will not zero the three digits, but round toward negative infinity.

To be robust with negatives, and keep the property of being able to round down to arbitrary base, you'd need something like =ROUNDDOWN(A1/1000)*1000

( 2018-03-12 10:00:41 +0200 )edit

@Mike Kaganski: Yep, that's a property of FLOOR() which I like because it allows to quantize number to another base than 10 (e.g. 1/4 or 1/8 when there are decimals).

( 2018-03-12 10:17:03 +0200 )edit

@ajlittoz: ...but the specification of FLOOR() in 'OpenFormual' is a mess. (However, at least it states itself that other applications may have implemented FLOOR() in a way prohibiting compatibility.)

( 2018-03-12 10:48:34 +0200 )edit

@Lupp: how is that so? I find it quite clear

( 2018-03-12 10:59:43 +0200 )edit

@Lupp: the mess is also in the embedded help. FLOOR() has a third argument (rounding towards zero or unsigned infinity) but you need to know the sign of the number to adjust accordingly second argument. I don't like this kind of context sensitivity.