Crystal Reports: Rounding

When you display a number in a Crystal Report, you have the option to round the number.

Crystal Reports does only traditional rounding where values 0.5 and above are rounded up to the nearest value, and values below 0.5 are rounded down. But there are other ways to do rounding and they may cause your report to display values that don’t match other systems or applications.

Some programming languages use "Bankers rounding," which was developed as a more accurate method of rounding. With Bankers rounding, values below 0.5 go down and values above 0.5 go up. Values of exactly 0.5 go to the nearest even number. So 12.5 will be rounded down to 12 (as in the example on the right) and a value of 13.5 will be rounded up to 14.

The justification for bankers rounding is apparent when you imagine a list with a typical distribution of even and odd numbers.

Such a list may show the numbers 12.0 to 13.0 in steps of 0.1. There are 9 values that need to be rounded. Traditional rounding moves 5 of the 9 values up and 4 down–always. This is biased (1/9 more up than down).

By moving 0.5 to the nearest even number it is now exactly 9 of the 18 numbers in each direction. It might not sound like much, but if it relates to interest calculations on billions of dollars, the difference in rounding calculations is still worth many millions of dollars.

Round a number up

Sometimes in a report, you would like to round a number up if it has any decimal places at all (not just 0.5 or over). You can do this with a formula of: 

If {number.field} - truncate({number.field}) <> 0 then truncate({number.field})+1 else 
{number.field} 

The advantage of this method is that you can use it in calculations. Otherwise, you are simply formatting the display. The logic is that is when you truncate a number, you throw away any decimals. Subtract the truncated number from the original number,and you can isolate the decimal part of the original number. 

If the decimals are 0 then use the original number as it has no decimals. Otherwise add one to the truncated value and you have rounded the number with decimals up to the next integer value. 

Thanks to Mike (mbarron) on Tek-Tips for answering this question. 

ToWords will round value up

The ToWords function will round the number prior to converting it to words. 
ToWords (53.6 , 0) will calculate the result as “Fifty Four” 

If you are trying to print cheques then you need to truncate the number prior to converting to words. The complete formula to convert the amount to words in dollars and cents is 

ToWords(truncate({table.payment}),0) + “ dollars” + //extract dollars only 
ToWords(remainder({table.payment}*100,100),0) + “ cents” //extract the cents and convert to words 

Another formula for rounding up

If you are rounding up with the Int function, consider an efficient method provided by Lisa on  Tek-Tip).

-int(-{table.field}) 
The Int function removes decimals and moves down to the next number.

So while:

Int(5.6) has a result of 5,
Int(-5.6) has a result of -6.

The logic in the above formula is 
1) Reverse the sign of the number 
2) Use Int to go down to the next number 
3) Reverse the sign again to calculate the rounded up result 

This technique works only on positive numbers, but sometimes that is all we need.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.