Tuesday 13 April 2010

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

Okay, this is just plain weird:

image

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:

image

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:

image

 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:

 image

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

The solution was found here:

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

No comments: