excelgoogle-sheets

Google Spreadsheet - Round down at 0.5, but round up above that

The regular "ROUND" function will round down when < 0.5, and will round up when >= 0.5

I need 0.5 to be rounded down, but anything above that to be rounded up.

So:
10.4 should be 10
10.5 should be 10
10.6 should be 11

Edit: Here is the solution i came up with

If the value to be rounded is in B1
And the decimal precision is in A1 (0 = no decimals, 1 = one decimal place, etc)
=IF(MOD(ABS(B1),(1/(10^A1)))<=0.5*(1/(10^A1)),ROUNDDOWN(B1,A1),ROUNDUP(B1,A1))

The ABS() makes sure it works with negative numbers.
The (1/(10^A1)) makes sure that my precision (which is a second argument to Google's rounding functions) scales my boundary condition (0.5) accordingly.
And the MOD() is what actually determines my boundary condition.

Edit2:

More elegant solution thanks to @Jayen

=ROUNDUP(B1 - sign(B1) * (10 ^ -A1) / 2, A1)

Solution

  • It is possible to create an IF statement that would do this =IF(A1-int(A1)>0.5,int(A1)+1,int(A1))

    But seams a strange request, the normal convention (in the west) is to round .5 up, not down.