Ask Your Question

How to zero the last 3 digits of a number (not rounding) [closed]

asked 2018-03-12 09:22:44 +0200

Libre comme un oiseau gravatar image

updated 2020-11-26 01:01:05 +0200

Alex Kemp gravatar image

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





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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-11-26 01:00:52.498659

2 Answers

Sort by » oldest newest most voted

answered 2018-03-12 09:54:50 +0200


edit flag offensive delete link 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.]

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

Well - terminologically speaking, yes :)

Mike Kaganski gravatar imageMike Kaganski ( 2018-03-12 10:36:59 +0200 )edit

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. (?)

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

answered 2018-03-12 09:56:43 +0200

ajlittoz gravatar image

updated 2018-03-12 10:16:36 +0200


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)

edit flag offensive delete link 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

Mike Kaganski gravatar imageMike Kaganski ( 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).

ajlittoz gravatar imageajlittoz ( 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.)

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

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

Mike Kaganski gravatar imageMike Kaganski ( 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.

+1 on your comment below.

ajlittoz gravatar imageajlittoz ( 2018-03-12 13:46:43 +0200 )edit

Question Tools

1 follower


Asked: 2018-03-12 09:22:44 +0200

Seen: 191 times

Last updated: Nov 26 '20