Ask Your Question

I need ROUND, but rounding down for tie…?

asked 2016-11-25 15:13:28 +0100

hedefalk gravatar image

updated 2017-12-10 17:05:24 +0100

erAck gravatar image

I'm struggling with this invoices I have were they have been rounding to nearest integer, but in the case of ties it rounds down. So for "reasons" I need a function f such that

f(0.6) = 1
f(0.4) = 0
f(0.5) = 0

Seems neither ROUNDDOWN (truncate) nor ROUND (rounding according to normal rules of rounding a tie upwards) is doing it for me.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-11-25 18:10:10 +0100

Lupp gravatar image

updated 2016-11-25 18:15:37 +0100

The most fundamental function of rounding is INT(SomeNumber) which always (independent of the sign of SomeNumber) returns the greatest integer (whole number) not greater than SomeNumber itself. This function should allow you to fully control the details of your rounding without relying on hidden properties of more "manufactured" functions.
Please note that the "greater than" relation is used here as mathematics handles it. -2 > -14.2 e.g. is true in this sense while -5 > -4 is false!
Your specific rounding is done by =-INT(-A1+0.5) for the value from A1 if positive values are assured.

If your values can also be negative you need to specify whether in this case rounding should be done based on the absolute amount (as ROUND would do) or based on the complete value (containing the sign).

See also attached.lof82504SpecialRounding_1.ods

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-11-25 15:13:28 +0100

Seen: 54 times

Last updated: Nov 25 '16