Ask Your Question
0

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

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

Libre comme un oiseau gravatar image

updated 2018-03-12 09:29:30 +0200

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

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

=ROUNDDOWN(A1;-3)

edit flag offensive delete link more

Comments

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

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

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

ajlittoz gravatar image

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

=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)

edit flag offensive delete link more

Comments

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 66 times

Last updated: Mar 12 '18