CEILING Function Appears to Round Numbers Incorrectly

Published: 06 Nov 2020
Last Modified Date: 29 Jan 2021


When using the CEILING function to round a number to the nearest integer of equal or greater value, numbers might appear larger than the expected number.
For example, when calculating CEILING(168.8/2.11), then it turned out to 81 instead of 80.

User-added image


  • Tableau Desktop


The workaround is to use ROUND() before CEILING like the following:



 In calculation, it shows as 80.000, but actually in binary it might be 80.00001. So the CEILING result turned out to be 81.

For example:
(168.8/2.11)*100000000000000 = 8,000,000,000,000,001.00000
STR(168.8/2.11) = 80.000000000000014

This behavior is by the design of computer calculations. As this behavior is not specific to Tableau Desktop, it is more fundamental to how computers store data, specifically what we consider "decimal" values.
With decimal values, translating different values to a binary representation, sometimes a simple representation in decimal does not translate perfectly to binary.
Did this article resolve the issue?