# I need ROUND, but rounding down for tie…?

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

Sort by » oldest newest most voted

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