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.
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.
=ROUNDDOWN(A1;-3)
@mikekaganski: 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.]
Well - terminologically speaking, yes
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. (?)
=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 @mikekaganskiâs remark)
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
@mikekaganski: 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: âŠ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: 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.