KNOWLEDGE BASE

CEILING Function Appears to Round Numbers Incorrectly


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

Issue

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

Environment

  • Tableau Desktop

Resolution

The workaround is to use ROUND() before CEILING like the following:
CEILING(ROUND(168.8/2.11,2))

 

Cause

 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?