I was doing some table calculations in Tableau and needed complete control over the rounding that was used. Tableau provides a single ROUND function. The exact specification of this (I believe) depends on the data source you are connected too, as it passes through to the underlying server. I needed to be able to look at the effect of the different methods for rounding so had to create some functions reproducing the different rounding methodologies.

Ken Black (@3danim8) has a great post on rounding and all the wonderful quirks of the different way here.

There are far too many options for dealing with rounding. Different ways to handle numbers at exactly 0.5: to round up (the one we are all taught), to round down, to round away from 0, to round towards 0, or banker’s rounding (rounding to the nearest even number). There are also some simpler rounding methods (e.g. Round Towards Zero).

This is a set of notes from my playing with rounding in Tableau, Excel and Alteryx. Hopefully useful!

Some Useful Functions and Behavioural Differences

While I am sure there are a myriad of ways to do this, these are the functions I found useful. They are:

• Round – the default rounding function, round a number to the nearest integer. This is used only for reference.
• Floor – round a number to the nearest integer lesser or equal than it.
• Ceiling – round a number to the nearest integer greater or equal than it.
• Abs – returns the absolute value of x
• Sign – returns 1 if the number is greater than 0, –1 if less than 0 or 0 if equal to zero.
• % – the modulo operator (gets the remainder of a division)
• Int – rounds a number to the nearest integer equal to it or closer to zero.

For some reason (and I have no idea why), the FLOOR and CEILING functions are not available in table calculations within Tableau. You can reproduce the functions using the modulus operator, %.

FLOOR([X]) = [X] – ([X] % 1)

CEILING([X])=[X] + ((1 – ([X] % 1)) % 1)

(The final ‘% 1‘ on the CEILING equivalent function avoids it from moving integers up.)

It is worth noting that Excel has slightly different behaviour for INT. This function in Excel returns the same as the FLOOR function within Tableau. The Excel equivalent to the INT function is TRUNC. Excel doesn’t have a modulo operator but does have the MOD function which performs the same function.

Alteryx has a similar set of functions. The CEILING function is called CEIL in Alteryx. There is not a modulo operator but there is a MOD function. However as this function only works on integers, it is not useful here. Fortunately there are no limitations on the CEIL and FLOOR functions so we can avoid using MOD. Alteryx doesn’t have an INT function, but again we can easily avoid using it. Finally, there isn’t a built in SIGN function in Alteryx but it can be trivially reproduced using the IIF function

SIGN([X])=IIF([X]>0,1,IIF([X]<0,-1,0))

One other thing in Alteryx is that the syntax for the Round function is also different to the others. The other environments take the number of decimal places. Alteryx takes a mult value and rounds the input to the nearest multiple of mult. This works as a great method when not just rounding to nearest integer (e.g. when rounding for round lots of shares in steps of 200).

Rounding Half Up

This is what Tableau does when you use the ROUND function (at least when connected to SQL Server). Easy enough to reproduce not using the ROUND function:

FLOAT(FLOOR([Unrounded]+0.5))

The FLOAT function is optional, but it converts the integer returned by FLOOR back to a decimal number. For reference, the ROUND function, returns a decimal number. This works great but as you can’t use the FLOOR function in a table calculation, it becomes a little more complicated for that case:

([Unrounded] + 0.5)-(([Unrounded] + 0.5) % 1)

Rounding Half Down

Basically symmetric problem to the Rounding Half Up, except you can’t use the ROUND function to help!

FLOAT(CEILING([Unrounded]-0.5))

This works great but again as you can’t use the CEILING function in a table calculation, it becomes a little more complicated:

[Unrounded] – 0.5 + ((1 – (([Unrounded] – 0.5) % 1)) % 1)

Rounding Half Towards Zero

This is the same as rounding the absolute value using the Rounding Half Down method and then applying the sign of the original number:

SIGN([Unrounded])*FLOAT(CEILING(ABS([Unrounded])-0.5))

Again once we have removed the CEILING function becomes a little more complicated:

SIGN([Unrounded])*(ABS([Unrounded])-0.5 + (1 -(ABS([Unrounded])-0.5) % 1) % 1)

Rounding Half Away From Zero

This is the same as rounding the absolute value using the Rounding Half Up method and then applying the sign of the original number:

SIGN([Unrounded])*FLOAT(FLOOR(ABS([Unrounded])+0.5))

Because we are dealing with positive numbers we can replace FLOOR with the INT function for table calculations:

SIGN([Unrounded])*FLOAT(INT(ABS([Unrounded])+0.5))

Again, because we are dealing with positive numbers if using this formula in Excel you can use the INT function and don’t have to switch to TRUNC.

Banker’s Rounding (Rounding Half To Even)

This is a fairly horrible process. Again we can deal with the absolute number and then re-apply the sign afterwards. In this case if the integer part is even we are rounding halves down and if it is odd then we are rounding halves up. As we are dealing with the absolutes, we can use INT instead of FLOOR.

First part is to determine odd versus even. The number is even if MOD(INT([Unrounded])) equals 0. Combining this with the formulae above gives:

SIGN([Unrounded])
* IIF(INT([Unrounded]) % 2 = 0, Ceiling(ABS([Unrounded])-0.5), INT(ABS([Unrounded])+0.5))

As a final step removing the CEILING function so it can be used on table calculations:

SIGN([Unrounded])
* IIF(INT([Unrounded]) % 2 = 0, ABS([Unrounded]) – 0.5 + ((1 – ((ABS([Unrounded]) – 0.5) % 1)) % 1),
INT(ABS([Unrounded])+0.5))

I am expecting there is a nicer way to do this but it achieved what I needed.

Other Rounding Methods

Rounding Down (i.e. rounding to the nearest integer equal to or lower than the unrounded value) is given by the FLOOR function:

FLOOR([Unrounded])

or

[Unrounded] – ([Unrounded] % 1)

Rounding Up (i.e. rounding to the nearest integer equal to or larger than the unrounded value) is given by the CEILING function:

CEILING([Unrounded])

or

[Unrounded] + ((1 – ([Unrounded] % 1)) % 1)

Rounding Towards Zero (i.e. rounding to the nearest integer equal to or close to zero) is given by the INT function. Remember the equivalent in Excel to INT is TRUNC, the alternative formula is provided as Alteryx has no INT function.

INT([Unrounded])

or

SIGN([Unrounded]) * FLOOR(ABS([Unrounded]))

Finally Rounding Away From Zero (i.e. rounding to the nearest integer equal to or further away from zero). There is no built in function to do this but it can be easily done by rounding the absolute value up and then reapplying the sign:

SIGN([Unrounded])*CEILING(ABS([Unrounded]))

or avoiding the CEILING function:

SIGN([Unrounded])*(ABS([Unrounded]) + ((1 – (ABS([Unrounded]) % 1)) % 1)

For what it is worth I have published a Tableau workbook to Tableau Public with these formulas. You can get it here