Round off functions

Dear Friends

Hope all of you remember our friend Bahadur Singh and the problems he faced in his routine day to day activities. This time around our friend was asked to do an analysis on the annual reports of various companies for the year 2005-06. On collecting the annual reports of various companies he was surprised when he saw casting (mathematical totaling) errors on the face of the report. He was not sure if these errors could have been avoided and hence he came to us for help. Let us first see the extract of the errors that he showed us:
Pictures to be included
(Please note that the tables shown are actual extracts from annual report for listed companies for the year 2005-06. However I have intentionally not disclosed the source of the same.)

Let us analyse why such errors happen so frequently and do we have a solution for the same.
In excel if we type 3.333333 in one cell and 3.333333 in second cell and then use a sum function for both these cells the result would be 6.666666. Now if we format all these three cells with two decimals format, the output is 3.33+3.33 = 6.67. This is where the casting errors happen. To make the same easy to understand, given below is an excel sheet for the same.

Input Data

Output Data

Formatted with 2 decimals
3.333333

3.333333

3.33
3.333333

3.333333

3.33
=SUM(A3:A4)

6.666666

6.67

Such errors are very common in many of our excel sheets. Further these errors are more prominent when we sign the audited accounts in multiples of thousands, lacs, millions, etc. The reason for such casting errors in excel is that the number that is visible to us (3.33 in above case) is not the same as the number that excel uses for calculation purpose (3.333333 in above case).

Bahadur Singh says that he knows few functions namely ‘round’ and ‘trunc’ in excel by which he claims that he can provide a solution for such problems. Let us analyse these functions one at a time.

1. ROUND function: This would round the number to specified number of digits.
Syntax: ROUND(Number,Digits)
Example:
Original Number

Function

Output
5346747.3984632

=ROUND(A4,2)

5346747.4000000







=ROUND(A4,4)

5346747.3985000







=ROUND(A4,-3)

5347000.0000000

Uses: The round formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. Hence this could also be used in computation of taxable income that is rounded off.

2. ROUNDUP function: A variant of normal ROUND function is ROUNDUP function. This would round the number to specified number of digits in upper direction.
Syntax: ROUNDUP(Number,Digits)
Example:
Original Number

Function

Output
5346747.3984632

=ROUNDUP(A4,2)

5346747.4000000







=ROUNDUP(A4,4)

5346747.3985000







=ROUNDUP(A4,-3)

5347000.0000000

Uses: The round up formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded on upper side. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. on the upper side.

3. ROUNDDOWN function: Similar to round up, a variant of normal ROUND function is ROUNDDOWN function. This would round the number to specified number of digits in lower direction.
Syntax: ROUNDDOWN(Number,Digits)
Example:
Original Number

Function

Output
5346747.3984632

=ROUNDDOWN(A4,2)

5346747.3900000







=ROUNDDOWN(A4,4)

5346747.3984000







=ROUNDDOWN(A4,-3)

5346000.0000000

Uses: The round down formula, as shown above, can be used to specify the decimal places to which a particular number should be rounded on lower side. It can also be used to display a particular number in thousands (as shown above), lacs, millions, etc. on the lower side.

4. TRUNC function: This function is used to truncate the additional decimal places or the rightmost digits.
Syntax: TRUNC(Number,Digits)
Example:
Original Number

Function

Output
5346747.3984632

=TRUNC(A4,2)

5346747.3900000







=TRUNC(A4,4)

5346747.3984000







=TRUNC(A4,-3)

5346000.0000000

Uses: This function is useful when instead of rounding off there is a need to truncate the additional digits after the decimal point. In addition to truncation of additional digits after the decimal point this can also be used to represent a particular number in thousands (as shown above), lacs, millions, etc. on the lower side. If one notices carefully the output of this function would be similar as the round down function.

Coming back to our case of casting errors, can we apply any of the above functions for the same? Bahadur Singh says that application of the above function would have avoided such casting errors. Hence the correct formula for adding 3.333333 and 3.333333 would be a combination of rounddown and sum. The same should be =ROUNDDOWN(SUM (A3:A4),2). In this case the output would have been 6.66 as required instead of 6.67.

How many of us use such functions to ensure that there are no rounding off errors in final accounts? But even after using such functions how many of us can be 100% sure that there would be no casting errors? Let us see what are the disadvantages of using such functions for final accounts:
ü The casting errors could be either ways, i.e. on lower side or on upper side hence we are not sure of whether to use plain round function or its variants. Hence this makes the formula very rigid.
ü The formula also becomes very cumbersome. It is impractical to enter such formula for a schedule like fixed assets where there has to be a sum function horizontally and vertically.

After reading this Bahadur Singh asks us, is there a way out? Discussed below is a setting in excel that will help all of you to resolve the casting errors completely without any rounding or truncating formulae.

Go to the following path:
Tools à Option à Calculation Tab.
In the lower left corner there are workbook options. In these options there is a setting for ‘Precision as displayed’ (As shown below). By default this box is blank. Just tick this box and press OK.

Picture to be included

There is a message that reads ‘Data will permanently lose accuracy’. Say OK and then find for any casting error in this file. You can be 100% sure that you will find none.

Important points to be noted for this setting:
1. This setting is applicable to the entire file and hence activation of this setting once will be applicable to all the sheets in the file.
2. The information message ‘Data will permanently lose accuracy’ is important to understand. It means that now the number will be read in the manner it is displayed. Hence number 3.333333 if displayed for 2 decimals will be read as 3.33 only and this will be permanent.
3. The setting once activated cannot be undone i.e. undo function is not applicable here. However one may decide to close the file without saving to bring back the original data.

Bahadur Singh was surprised that he was ignorant about such a simple setting available in excel to solve all such problems. He is sure that he would not make casting errors as done by many of us.

Hope that the fixed asset schedule would not be as difficult as it has been. Keep a look at this space, as there are more such tricks to come that would make your life much easier.