Rounding problems

Posted by Cindy on September 05, 2001 10:07 AM

In a payroll spreadsheet I am multiplying, formatting to two decimal places and it causes problems when summing. Example: $51.50 * .062 = $3.193, format to 2 decimals = $3.19; now add $3.19 four times down a column and it shows a sum of $12.77. It has added $3.193 four times and come up with $12.77 instead of adding only $3.19 and summing to $12.76. Is there a global rounding solution or do I have to also use my calculator and figure out which columns are not giving me the answer I want? Thanks a bunch! Cindy

Posted by Mark W. on September 05, 2001 10:18 AM

You can choose the Tools | Options... Calculation
menu command and check "Precision as displayed".

Posted by Cindy on September 05, 2001 10:30 AM

Thank you! It says it may lose accuracy. Is this a major problem?

Thanks, again.

Posted by Barrie Davidson on September 05, 2001 10:38 AM

You could also try

changing your formula to ROUND(51.50*0.062,2), rounding your results to 3.19

Barrie

Posted by Mark W. on September 05, 2001 12:01 PM

Lesser precision is exactly what you've requested.
=51.5*.062 produces 3.193, but you need it to be
3.19 so that your displayed sum total will be
correct. Keep in mind that unless you use
"Precision as displayed" or Excel's ROUND
worksheet function the internal representation
of a value could differ from its displayed value.