Excel 2010 – Multiplies Cells Containing Formula Results with a Slightly Off Result?

Okay, this is just plain weird:

That calculation is taking a raw number “8” and multiplying it by the result of a series of calculations found in the second cell with the result of “$20.55”.

The end result states that 8 * 20.55 is 164.38 which for most of us seems to be really wrong. Especially for a program that is supposed to do this right!

So, just how do we “fix” this?

A quick search turned up the culprit:

Under File –> Tools –> Advanced we find the Set precision as displayed setting.

When we enable it and click OK Excel becomes quite unhappy about it:

 Microsoft Excel

Data will permanently lose accuracy.

Well, be that as it may, that “loss in accuracy” helps the old human brain to see what it really needs to see:

 

That is the expected result! This works for all versions of Excel too.

The solution was found here:

• CPearson.com: Rounding Errors In Microsoft Excel 97

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists
Co-Author: SBS 2008 Blueprint Book

*Our original iMac was stolen (previous blog post). We now have a new MacBook Pro courtesy of Vlad Mazek, owner of OWN.

Windows Live Writer