We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How do I round down to the nearest whole dollar

asked 2020-07-27 17:46:31 +0200

gis99r0 gravatar image

How do I round down to the nearest whole dollar? I am dividing within a cell.

edit retag flag offensive close merge delete


"down" or "to the nearest"?

Mike Kaganski gravatar imageMike Kaganski ( 2020-07-27 17:50:16 +0200 )edit

Nearest downwards, probably?

Lupp gravatar imageLupp ( 2020-07-27 18:12:32 +0200 )edit

I do apologize, but I am relatively new to LibreOffice. How do I write 'nearest downward' in an algebraic equation? I have a cell value "=I9". I9 is currently $488.70. The new cell is in K6. But I want the value rounded down to the nearest whole dollar.
When I use that cell in future calculations, it needs to reflect the value of being rounded down value. $488.00, not the $488.70. Ex. '=K6/3' should be $162.67, not $162.90.

gis99r0 gravatar imagegis99r0 ( 2020-07-27 18:25:32 +0200 )edit

some philosophy for the early morning:

Q1: what's the difference between 'round down' and 'round down to the nearest'? does round down skip some whole numbers sometimes?

Q2: what is 'down'? what about negative values? is 'down' 'towards zero' there? or 'down' towards the next number with less value, thus '-10,6' becoming '-11'? (down in australia is similar but another direction than in europe)

taking 'x,5', floating point deviations and bankers rounding into account one can talk hours about rounding ...

newbie-02 gravatar imagenewbie-02 ( 2020-07-28 05:07:49 +0200 )edit

@newbie-02: See the amendment to my answer.
It does, however, not comment on the question if semantics may be changed by trying to get things even clearer. (A case of ἓν διὰ δυοῖν probably? That's rhetoric.)
Concerning the "down" vs "toward zero": That's partly a case of general sloppiness of spoken languages, but also probably common human heritage from times when negative numbers still not existed. (Using the term "exist" here is thought to be a puzzle for philosophers.) Disregarding the ways mathematics currently handles the related terms may also be common heritage.

Lupp gravatar imageLupp ( 2020-07-28 11:12:43 +0200 )edit

Interesting discussion. A case in point, for example (LO v6.0.7.3) :

INT(3.14) = 3
INT(-3.14) = -4

Not what I expected, but I am not a mathematician.

ve3oat gravatar imageve3oat ( 2020-07-28 16:00:19 +0200 )edit

This simply is "down" the way mathematics treats it vs "toward zero" as widespread usage may prefer in the context.
Math: "down" for "toward negative infinity"
Contrary: "down" for "toward lower absolute amount".
Everything concerning cases where math would apply ABS() or talk of absolute amounts to be precise tends to be messed up in everyday speaking.
Alas! Mathematics also is treating many terms "context sensitive".

Lupp gravatar imageLupp ( 2020-07-28 16:28:32 +0200 )edit

Thanks for that, @Lupp. Alas, indeed! Until now, my view has been rather simplistic, and I have regarded an integer as simply that part of a number without the fractional part. So any number n is INT(n) + FRAC(n), in old FORTRAN notation. So INT(-3.14) was just -3. But I learn something every day. Better now to stick with ROUNDUP() and ROUNDDOWN().

ve3oat gravatar imageve3oat ( 2020-07-28 18:20:11 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-07-27 18:15:51 +0200

Lupp gravatar image

updated 2020-07-30 21:45:29 +0200

===Edit 2020-07-29 about 07:40 UTC===
From the recent comment by @newbie-02 I learned that I wasn't precise enough with the following lines. I tried to fix the issue, but as already stated in a different place, everyday laguage is basically inapt to be precise and concise at the same time concerning related topics.

Nearest integer: =ROUND(expression;0)
Nearest integer upwards, but not greater concerning the absolute amount: = ROUNDUP(expression;0)
Nearest integer downwards, but not less concerning the absolute amount: = ROUNDDOWN(expression;0)

===Edit 2020-07-28 about 09:25 UTC===
I took the opportunity to collect some examples, and also the related specifications in force for LibreOffice Calc.
Having read the specifications you may understand my personal habit of avoiding next to every rounding function except INT().

See : (Example removed due to errors)

===Edit 2020-07-30 about 07:25 UTC===
As @newbie-02 hinted in a comment, formulas used in my example sheet contain errors.
I obviously wrote too many formulas in a short time. This may endanger brain operability.
I will remove the bad example. If I find the time, I will replace it with a fixed one.

edit flag offensive delete link more



There is also =INT() which specifically rounds a number down to the nearest, but not less than, whole number (integer).

ve3oat gravatar imageve3oat ( 2020-07-27 19:02:44 +0200 )edit

some headaches for the late night ...
such a simple question, so much uncertainty ...
@Lupp: having read your comments and answer, and having played with LO calc 7.1 i'm now totally confused:

formula:                            -->  result: 
=INT(-3,14)                      -->  -4
=ROUND(-3,14;0)            -->  -3

=ROUNDUP(expression;0) 'Nearest integer upwards, but not greater' 
=ROUNDUP(-3,14;0)       -->  -4

=ROUNDDOWN(expression;0) 'Nearest integer downwards, but not less'
=ROUNDDOWN(-3,14;0) -->  -3

and the difference between 'down' and 'down to the nearest' is still unclear to me, but i'm reasonably tired ...
and yes, of course, i'd really like to avoid such new minefield ... but ... :-( ... rounding is often suggested as the only remedy against 'floating point/decimal' errors ... thus this is another argument to finally implement IEEE 754-2008 ...

newbie-02 gravatar imagenewbie-02 ( 2020-07-29 00:57:49 +0200 )edit

Answer edited!
(and)... Even the nearly unacceptably complicated term "absolute amount" may be not clear enough for some users. We have a tradition of thinking that "numbers are positive and signs communicate a quality". This is consistent with former handling of bank transactions and accounts where only amountsoccured, but were split into different columns labeled "in", "out" or similar. See https://en.wikipedia.org/wiki/Negativ... . Bad traditions in teaching and a general tendency of sticking to bad usage contributed to the problem.
We all could know about the overwhelming advantages of accepting negative numbers and of using them in thousands of situations, but many refuse.
This is related to the refusal (specifically frequent in USA and UK) to accept SI units and ISO standards in everyday life. Only legislative action helped where taken. Do you know the UK "personal" tax year? See https://en.wikipedia.org/wiki/Fiscal_...

Lupp gravatar imageLupp ( 2020-07-29 09:38:10 +0200 )edit

Quoting @newbie-02: "...the difference between 'down' and 'down to the nearest' is still unclear to me,"
My bad. I tried to make things "even clearer", and obviously failed. I didn't want to distinguish the terms.

Lupp gravatar imageLupp ( 2020-07-29 09:42:44 +0200 )edit

Quoting @newbie-02: "...another argument to finally implement IEEE 754-2008 ..."
You are talking of "next, and if ambiguous even"?
Well we did it that way in 1965 experimentally for numbers represented dyadic.
I don't know, but would assume this is generally implemented for FPU nowadays.
Would you suggest to use it respectively with decimal representatin?
In 2020, Germany, most people think "round up if 5,6...9" is something originating in mathematics.

Lupp gravatar imageLupp ( 2020-07-29 10:00:08 +0200 )edit

nice discussion ... @Lupp: 'concerning the absolute amount:' - thus roundup and rounddown do not! - as i first understood - work 'mathematical up and down', but - as i tested - the other possibility of understanding up and down, regarding absolute value, and thus 'towards' or away from zero,
IEEE 754: it's just something that occurs frequently, small errors with normal floats are caused by the limited binary representation in machines, these sometimes 'come to the surface', and every clearing of an irritated user costs a lot of time, as a remedy it is often recommended to round the results, and since you had just argued against rounding, it came to my mind, IEEE 754-2008 should work without such errors, in a few processors in hardware, otherwise as software emulation, it could make LO calc more 'financially sound' as an additional datatype ... imho ... your sample ... would you mind rechecking, e.g col P, size ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-07-29 16:48:30 +0200 )edit

One better. As I recall, if the last decimal place is exactly "5", then you round up only if it makes the preceding digit even; otherwise, you round down. The rationale is to avoid introducing bias into statistical calculations. But, this is hardly relevant to the OP's original question.

ve3oat gravatar imageve3oat ( 2020-07-29 16:50:30 +0200 )edit

@Lupp: 'everyday laguage is basically inapt to be precise and concise at the same time' - whow!, that's quite near to Heisenberg,
@ve3oat: reg. OP: yes, we are somewhat off from the scope of the question, but it's bringing things forward, and OP should have info enough to solve nearly any rounding problem while making it worse with the same steps,
i thought rounding 'x5' to even, or "next, and if ambiguous even" as @Lupp named it, is 'bankers rounding', have seen interpretations now bankersrounding rounds any! fraction to 'even', what about bankersround(1,0;0)?
@Lupp: nowadays FPUs and compiler consist of that much switches and options, i'm not sure if there is one for '0,5 up vs. 0,5 to even', for bankers rounding Jean-François Nifenecker thought adding a tool to calc is neccessary,
imho handling of such cases is quite difficult with FP's ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-07-30 11:07:11 +0200 )edit

@newbie-02: Concerning your philosophical interests I would suggest you find a way to have a more private communication. You may succeed via the impressum of http://psilosoph.de.

Lupp gravatar imageLupp ( 2020-07-30 11:26:43 +0200 )edit

@Lupp: will write when spare time, but did you check that: "your sample ... would you mind rechecking, e.g col P, size and 'direction' of the 'add help' seems strange to me" or did it fell aboard in all this philosopy?

results are strange as well - imho - 'usual' rounding of -0,11111111 to 5 decimals shouldn't result in 49999,88888

newbie-02 gravatar imagenewbie-02 ( 2020-07-30 12:45:32 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-07-27 17:46:31 +0200

Seen: 315 times

Last updated: Jul 30 '20