Controlling rounding and decimal precision in numeric expressions

In calculations that involve multiplication or division, Analytics rounds the result to the larger number of decimal places in the two operands. This method of rounding is associated with the fixed-point arithmetic that Analytics uses for evaluating most numeric expressions.

For multi-operand expressions (such as a*b/c ), rounding is applied at each stage in the expression, starting with the first evaluated stage, and repeating until the expression is completely evaluated.

Note

Not being aware how rounding works in Analytics is one of the most common causes of computational errors.

Fixed-point arithmetic

The rounding behavior in Analytics is associated with the fixed-point arithmetic that Analytics uses for numeric operations (with the exception of financial functions). Analytics uses fixed-point arithmetic for two reasons:

It improves processing speed
It allows user control over decimal places and rounding

Rounding in multiplication

Consider the expression 1.1 * 1 .1. The correct answer is 1.21. However, Analytics rounds the result to one decimal place because that is the larger number of decimal places in the operands.

1.1 * 1.1 = 1.2

If one of the operands has two decimal places, Analytics rounds the decimal portion of the result to the larger number of decimal places in the operands. In this particular example, no rounding is required:

1.10 * 1.1 = 1.21

Rounding in division

Consider the expression 7/3. The correct answer is 2.333... However, Analytics rounds the result to zero decimal places because neither operand has any decimal values.

7/3 = 2

If one or both operands have decimal places, Analytics rounds the decimal portion of the result to the larger number of decimal places in the operands:

7/3.00 = 2.33
7.000/3.00 = 2.333

Adding decimal places to control rounding

The easiest way to control rounding, and achieve a desired decimal precision, is to multiply an expression by 1, followed by the number of decimal places of precision that you want in the result. For example, multiply by 1.0000 to ensure that a result is accurate to four decimal places.

Example

Problem

In the following expressions, Analytics rounds the result to two decimal places, which is not precise enough for your requirements:

7.21 * 2.33 = 16.80

7.21 / 2.33 = 3.09

Solution

To increase the precision of the result, you multiply by 1 followed by the number of decimal places of precision that you want:

1.0000 * 7.21 * 2.33 = 16.7993

1.000000 * 7.21 / 2.33 = 3.094421

Caution

Position 1 at the beginning of an expression. If you position 1 anywhere else, the precision adjustment may not work because the precision of the first two operands to be evaluated has already caused rounding to occur:

7.21 * 2.33 * 1.0000 = 16.8000

7.21 / 2.33 * 1.000000 = 3.090000

Be careful when using parentheses

Be careful when using parentheses to specify the order of mathematical operations. If you do use parentheses, the precision adjustment may not work because the precision of the operands in parentheses has already caused rounding to occur:

1.0000 * (7.21 * 2.33) = 16.8000

Incorporating the 1 inside the parentheses can solve the problem:

(1.0000 * 7.21 * 2.33) = 16.7993

Rounding behavior in multi-operand expressions

Rounding behavior and decimal precision work the same way regardless of how many operands an expression contains. Analytics rounds the result to the larger number of decimal places in two operands as it evaluates expressions in a pairwise manner.

However, because of the way cumulative rounding works in a multi-operand expression, the decimal precision established by the first two operands to be evaluated is the precision that applies to the expression result.

Another characteristic of cumulative rounding is that the loss of decimal precision increases at each stage of a multi-operand expression.

Example of two-decimal-place precision

The table below illustrates how Analytics applies rounding as it calculates the following multi-operand expression:

1.1 * 1.12 * 1.123 * 1.1234 = 1.5514

The larger number of decimal places in the first evaluated stage of the expression is two ( 1.1 * 1.12 ). This two-decimal-place precision persists throughout the remainder of the multi-operand expression (indicated by the digits in red).

The Result difference column shows how the cumulative loss of precision increases at each successive stage of evaluation.

Analytics calculations (in evaluation order)	Analytics result (rounded)	Unrounded calculations (in evaluation order)	Unrounded result	Result difference
1.1 * 1.12	1.23	1.1 * 1.12	1.232	0.002
1.23 * 1.123	1.381	1.232 * 1.123	1.383536	0.002536
1.381 * 1.1234	1.5514	1.383536 * 1.1234	1.5542643424	0.0028643424

Analytics calculations

(in evaluation order)

Analytics result

(rounded)

Unrounded calculations

(in evaluation order)

Unrounded result

Result difference

1.1 * 1.12

1.23

1.1 * 1.12

1.232

0.002

1.23 * 1.123

1.381

1.232 * 1.123

1.383536

0.002536

1.381 * 1.1234

1.5514

1.383536 * 1.1234

1.5542643424

0.0028643424

A closer look at precision

In the ACL result (rounded) column, all decimal places after the first two are imprecise when compared to the corresponding Unrounded result. The degree of imprecision is likely your main concern when performing numeric calculations as part of data analysis.

The rounded results are not imprecise in the context of the specific pairwise calculations that produce them. For example, 1.23 * 1.123 = 1.38129, which rounded to three decimal places, in accordance with the rule, is 1.381. However, 1.23 was previously rounded from 1.232, which means that the specific pairwise calculation already contains a degree of imprecision.

Example of five-decimal-place precision

The table below illustrates how Analytics applies rounding after the addition of 1.00000 to specify five decimal places of precision:

1.00000 * 1.1 * 1.12 * 1.123 * 1.1234 = 1.55427

The larger number of decimal places in the first evaluated stage of the expression is five ( 1.00000 * 1.1 ). This five-decimal-place precision persists throughout the remainder of the multi-operand expression (indicated by the digits in red).

Analytics calculations (in evaluation order)	Analytics result (rounded)	Unrounded calculations (in evaluation order)	Unrounded result	Result difference
1.00000 * 1.1	1.10000	1.00000 * 1.1	1.10000	0.00000
1.10000 * 1.12	1.23200	1.10000 * 1.12	1.23200	0.00000
1.23200 * 1.123	1.38354	1.23200 * 1.123	1.383536	0.000004
1.38354 * 1.1234	1.55427	1.383536 * 1.1234	1.5542643424	0.0000056576

Specifying the order of operations

Placement of parentheses plays an important role in determining the level of precision obtained in a calculation. Be careful when using parentheses to override the normal order of mathematical operations.

Calculating one day’s interest

The scenario

You need to calculate one day’s interest on $100,000 at 12% per annum.

One approach

You could first calculate the interest rate per day, and then multiply the daily rate by 100,000. However, problems with rounding arise with this approach.

100000 * (0.12/365) = 0.00

Analytics first divides 0.12 by 365, which based on the rules of rounding in Analytics results in 0.00. The true result is 0.00032876712..., but because the result is rounded to two decimal places all the subsequent digits are lost.

The rounded result is then multiplied by 100000, yielding 0.00, even though the correct answer is 32.876712...

An alternate approach

You could first calculate the total amount of interest for the year, and then divided the amount by 365. This alternate approach avoids problems with rounding.

100000 * 0.12/365 = 32.88

With the parentheses removed, the results at each stage of the calculation remain greater than 1, avoiding any decimal rounding issues, and the answer is correct to the penny (two decimal places).

Change how decimal precision works

You can use the SET MATH command to change how decimal precision works in numeric expressions. By default, Analytics uses the larger or maximum number of decimal places when evaluating two operands. Using the maximum number preserves the greatest amount of precision at each stage of an expression.

Using SET MATH in the command line or in an Analytics script changes the default behavior for the duration of the Analytics session. In the summary of options below, the different results for the expression 1.275 * 1.3 are shown. The actual unrounded result is 1.6575.

Command	Description	Result of 1.275 * 1.3
SET MATH FIRST	use the number of decimal places of the first operand in a pair of operands	1.658
SET MATH LAST	use the number of decimal places of the last operand in a pair of operands	1.7
SET MATH MIN	use the minimum number of decimal places in a pair of operands	1.7
SET MATH MAX Default	use the maximum number of decimal places in a pair of operands	1.658
For detailed information about SET MATH, see SET command.