Excel Rounding When Not Asked To

Occasional Visitor

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.

RobMartin5450_0-1639510286746.png

 

 

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.