Forum Discussion
Rounding issue
Answer Getting From the excel
Validating with online application the answer is different.
The equation is used to round the value is
If the cell with the formula uses General as number format, Excel will not display trailing zeros after the decimal point. So 0.0130 will lose the 0 at the end and be displayed as 0.013.
You would have to specify the number of decimal places to be used in a custom number format. You can do this by creating a series of conditional formatting rules.
Select the cell with the formula.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=CEILING.MATH(IFERROR(LEN(I4)-SEARCH(".",I4),0))=1
Click Format...
Activate the Number tab.
Select Custom.Enter 0.0 in the Type box.
Click OK, then click OK again.Repeat these steps, but with the formula
=CEILING.MATH(IFERROR(LEN(I4)-SEARCH(".",I4),0))=2
and 0.00 as custom format.
Repeat them again, but with the formula
=CEILING.MATH(IFERROR(LEN(I4)-SEARCH(".",I4),0))=3
and 0.000 as custom format.
Etc., with =4, =5, ... to the maximum number of decimal places that you will need.
If the cell with the formula uses General as number format, Excel will not display trailing zeros after the decimal point. So 0.0130 will lose the 0 at the end and be displayed as 0.013.
You would have to specify the number of decimal places to be used in a custom number format. You can do this by creating a series of conditional formatting rules.
Select the cell with the formula.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=CEILING.MATH(IFERROR(LEN(I4)-SEARCH(".",I4),0))=1
Click Format...
Activate the Number tab.
Select Custom.Enter 0.0 in the Type box.
Click OK, then click OK again.Repeat these steps, but with the formula
=CEILING.MATH(IFERROR(LEN(I4)-SEARCH(".",I4),0))=2
and 0.00 as custom format.
Repeat them again, but with the formula
=CEILING.MATH(IFERROR(LEN(I4)-SEARCH(".",I4),0))=3
and 0.000 as custom format.
Etc., with =4, =5, ... to the maximum number of decimal places that you will need.
