Forum Discussion
Excel Rounding When Not Asked To
Excel is rounding a total when not asked and I am unable to make it stop.
Formula is =IF(H21="","",(I21*J21))
H21=1 I21=14.58 J21=78
14.58*78=1137.24
Excel returns $1137.50
I changed the currency format to number and general with no change. I cleared all formatting through the editing section of the home tab. Not sure where to go from here. Please help.
1 Reply
RobMartin5450 .... Ironically, the problem is just the opposite: Excel is __not__ rounding where you intended it to, apparently.
PS.... If the following does not pan out, please attach an Excel file that demonstrates the problem.
The quick-and-dirty solution is:
=IF(H21="", "", ROUND(I21, 2)*J21)
or better:
=IF(H21="", "", ROUND(ROUND(I21, 2)*J21, 2))
Even better: Use ROUND in the calculations in column I (or in the cell they reference). But it would still be good to __also__ use ROUND(I21*J21, 2) in the formula above.
-----
The problem is: The values in column I are not what they appear to be.
When I21*J21 appears to be 1137.50, the actual value in I21 is between 14.5832692307692 and 14.5833974358973, which appear to be 14.58 when formatted to display only 2 decimal places.
When I22*J22 appears to be 2210.00, the actual value in I22 is between 23.02078125 and 23.0208854166666, which appear to be 23.02 when formatted to display only 2 decimal places.
I assume that the values in column J are indeed the integers that are displayed. But maybe not.
Also note that the result of I21*J21 and I22*J22 only appear to have only 2 decimal places because of formatting.
You can see the additional decimal places by changing the cell format temporarily. Alternatively, enter formulas of the form =I21 & "".
-----
Caveat: Someone might suggest setting the Precision As Displayed option instead of using ROUND. I deprecate the use of that option. But if you choose to experiment, be sure to make a back-up copy of the Excel file before setting the option. Merely setting PAD can change some constants irreversibly.
