Overview
Application Scenario
In report creation, you may need to sum the data of a data column. For example, the numerical data column of income amount contains data with no more than two decimals, but the sum result (using the SUM formula) of the data column is 123456.409999996, a number with more than two decimals.
In this case, you may wonder why the result is not a number with two decimals. How to handle this situation when your report places a high priority on the valid decimals such as the amount summary where the total amount should be a number with no more than two decimals?
Function Description
In this case, you can use the ROUND function to return the calculated result rounded to a specified number of decimal places.
Example
Function Instruction
Overview
Syntax | ROUND(number,num_digits, boolean) | To return the number rounded to the specified number of decimal places. |
---|---|---|
Parameter 1 | number | The number to be rounded. |
Parameter 2 | num_digits | The specified number of decimal places to be rounded. If the value of num_digits is greater than zero, the number is rounded to the specified number of decimal places. If the value of num_digits equals to zero, the number is rounded to the nearest integer. If the value of num_digits is smaller than zero, the number is rounded to the nearest integer smaller than the number itself. |
Parameter 3 | boolean | Due to precision loss in floating-point numbers, the calculation result may contain figures such as 9999 or 0000. Therefore, a third parameter is added to control whether to remove 9999 and 0000. false means to remove data with 9999 and 0000, and true means to retain the data with 9999 and 0000. If the parameter is empty, the parameter value is false by default.
|
Notes
The third parameter is only available for JAR packages of the version published on 2020-07-08 and later versions.
The number parameter can be set to strings in JAR packages of the version published on 2020-07-08 and later versions.
Example
Formula | Result |
---|---|
ROUND(2.15, 1) | 2.2 |
ROUND(2.149, 1) | 2.1 |
ROUND(-1.475, 2) | -1.48 |
ROUND(21.5, -1) | 20 |
ROUND(1.99999999, 8) | 2 |
ROUND(1.99999999, 8, true) | 1.99999999 |
Procedure
The following figure shows the procedure of summing the data in the Sales_Volume column.
The second calculation result is more precise.
In this case, you can use the ROUND function to round the result to the second decimal place, which is the method used in cell B5.