How do I round down to the nearest whole dollar

[newbie-02]

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 …

[Lupp]

@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.

[ve3oat]

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.

[Lupp]

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”.

[ve3oat]

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

[Lupp]

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

[ve3oat]

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

[newbie-02]

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 …

[Lupp]

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 amounts occured, but were split into different columns labeled “in”, “out” or similar. See Negative number - Wikipedia . 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 Fiscal year - Wikipedia

[Lupp]

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]

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.

[newbie-02]

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 and ‘direction’ of the ‘add help’ seems strange to me

[ve3oat]

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.

[newbie-02]

@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, while e.g. 0,5 0,25 have distinct representation already 0,05 is an endless binary fraction, and thus subject to round before calculation

[Lupp]

@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.

[newbie-02]

@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

[ve3oat]

@newbie-02 : BANKERSROUND() Yes, love it! Most people probably think this stuff is pretty boring, but these ideas might explain why the bank and credit card companies make so much more money than the rest of us. Something to think about …

[Lupp]

Quoting @newbie-02: “…, it could make LO calc more ‘financially sound’ as an additional datatype … imho … your sample … would you mind rechecking, e.g col P, size and ‘direction’ of the ‘add help’ seems strange to me”
Sorry. I simply didn’t undserstand this.
Now I tried again, and still did not understand, but thoroughly looking at the formula used in some columns of my example sheet I found errors resulting (among all the other wrong results) in the bad result for your examptle. .
But why should I worry about errors in my formulas if clever users find them anyway?
I will remove the bad example, and replace it with a better one if I find the time.

[newbie-02]

@Lupp: it’s been two points:

• imho IEEE 754-2008 could serve better for decimal (and financial!) calculations,
• probably “=INT(($A5+10^-$J5/2)*10^$J5)*10^(-$J5)” (observe ‘-’ sign in 10^-$J5/2) could serve better for your col P, the result for -1,5 could still be discussed,
  a good paper on rounding:, the graph there: gave one idea, OP’s expression ‘down nearest’ could possibly be meant as ‘nearest downwards’, which - i didn’t find it in the text, just concluded from the graphic - could be an abreviation for “next and if ambiguous down” … ???

[Lupp]

Yes. For the columns H and P I had missed to enhance the “primitive” version I had started with, and in column P the missing ChangeSign was an obvious cursoriness. This was fixed soon, but there also was missing the implementation of the “usual” way of rounding “toward or offward” zero where I tried to find a “more cleverer” way. Since I didn’t find one this also was fixed the standard way. After all I had used such rounding in advance - for principal reasons.
I didn’t yet post the result because I still consider If I should add two or more columns concerning the uncommon rounding “to even final digit if ambiguous”.
Do you actually know if FPU are capable of rounding the dyadic representation (IEEE 754 Double) this way? I don’t know a compiler setting to that effect, and would doubt if there can be a sufficiently efficient implementation if not the FPU supports it with native means.
And decimals? …