How Stop Rounding Errors?

  • Thread starter Thread starter Al Franz
  • Start date Start date
A

Al Franz

On financial worksheets I am getting fractional rounding errors. How can
you format a cell so that if =(A1*B1) is the formula the result in the cell
will be a value such as $50.45 and not $50.457 even though it is formatted
for 2 decimal places.
 
A B B
50.457 =ROUND(A1,2) 50.46 (Note the 6)
50.457 =ROUNDDOWN(A2,2) 50.45
50.457 =FLOOR(A3,0.01) 50.45
50.457 =INT(A4*100)/100 50.45
 
Al Franz said:
On financial worksheets I am getting fractional rounding errors. How can
you format a cell so that if =(A1*B1) is the formula the result in the cell
will be a value such as $50.45 and not $50.457 even though it is formatted
for 2 decimal places.
Click to expand...

Tools > Options, Calculation tab, check 'Precision as Displayed'. This will
make *ALL* calculations subject to fixed point arithmetic, so you'd need to
check that the implicit rounding/truncation does what you want. But if it
does *AND* you never have to deal with reconciling individual terms in NPV
calculations (compound interest calculations are a royal pain with fixed
point), then this would be the best approach.
 
Try the formula =ROUND(A1*B1,2)
Cheers
Rob

Harlan Grove said:
Tools > Options, Calculation tab, check 'Precision as Displayed'. This will
make *ALL* calculations subject to fixed point arithmetic, so you'd need to
check that the implicit rounding/truncation does what you want. But if it
does *AND* you never have to deal with reconciling individual terms in NPV
calculations (compound interest calculations are a royal pain with fixed
point), then this would be the best approach.
Click to expand...
 
Back
Top