[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Unexpected Results from Excel 2007

Posted on 2011-10-20
8
Medium Priority
?
267 Views
Last Modified: 2012-05-12
While comparing two worksheets, I came across an interesting and disturbing problem. The two worksheets appear to have values that are exactly 1000 times different for corresponding values.  However, a subtraction of the two values does not always result in zero as you might expect. This odd result seems to caused by the use of parens in a cell formula.

For instance, consider a value in cell A1 with value 0.000702 and a value in B1 of 0.702 in another worksheet.  [Note:  I am looking at the values in the cells themselves, not the formatted results.]  These two values appear to be different by exactly a factor of 1000. However, if you apply the formula (B1/1000 – A1), which you would expect to be zero, you get a nonzero result -- specifically, -1.1 * 10^-19.  Clearly, very close to zero, but not quite.  Interestingly, if you do the same computation without the parens, such as B1/1000 – A1, the resulting value is zero.

This strange result occurs for many, but not all, cells I am working with.
0
Comment
Question by:cmmcginn
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 37001971
I get the same result in 2010

Excel is well known for glitchy results like this - google it and you will find many such gotchas, some dating back to the original version. As you note some of them are display only problems with the actual result being correct:-
http://www.cinemablend.com/technology/Microsoft-Excel-2007-Gets-Buggy-6546.html

But basically precision numbers are  clearly risky business in some areas of excel - now if only someone could say exactly which areas!

0
 
LVL 50

Expert Comment

by:barry houdini
ID: 37002139
This is caused by "floating point arithmetic" - the way that Excel calculates - like with decimals where 1/3 can't be accurately displayed as a decimal so some numbers can't be accurately rendered in binary, leading to these sort of errors - it's not deemed to be a bug  - see here

regards, barry
0
 
LVL 9

Expert Comment

by:experts1
ID: 37002160
Whenever you enter a formula in any programming language,
it is best to always enclose each specific segment of the formula
in parenthesis, to avoid confusion and unexpected results.

Otherwise, your result will be at the mercy of the default logic
of the program being used.

I would state my formula as follows:

(B1/(1000 – A1))
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:cmmcginn
ID: 37004157
As noted, the problem is caused when I use parentheses: A/1000 - B gives correct answer, while (A/1000 - B) gives the wrong answer.

Because the use of parentheses causes the error, how Excel calculates floating point numbers also do not apply to this situation. The issue seems to be that Excel is performing a different calculation when parentheses are present in the formula, even though the parentheses do not change the order of operations.
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 37005294
experts1:

your formula would give completely wrong result as it would deduct A1 from 1000 before dividing B1 by the result.

cmmcqinn:

I have found similar issues when adding up data in different ways. For example I recall a situation whereby I was doing a number of sumif formulas on a table of data. I then added up the total of the sumif formulas and compared with a sum of the original data to ensure I had got all options in the sumif formulas. I found that the total of the sumif bunch didn't equal the sum of the complete data by an amount so small as to be negligible but it meant the result didn't show as zero.

Then I had conditional formatting to highlight those where there was potentially sumif options missing and I had to tweak it so that the condition wasn't just "<>0 "but "<-0.001 or >0.001".

Thanks
Rob H
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 37005299
To add to the example that I mentioned in my previous comment, all values being summed were hard coded values and not formula driven and all were only to two decimal places yet it still showed the slight difference when adding up in two ways.

Thanks
Rob H
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 37005609
Floating point arithmetic is still the issue, I think, but, yes, the calculations are different due to the brackets, see this link, specifically post 4 explanation by Jerry W Lewis of the difference between

=(0.09+0.01-0.1)

and the same version without the brackets

Jerry explains it a lot better than I could but to summarise:

In the version without brackets, when the last operation is a subtraction, the comparison is made to 15 decimal places, when the brackets aren't there the comparison uses more decimal places and the result isn't zero. Effectively by using more precision an error is made!

regards, barry
0
 

Author Closing Comment

by:cmmcginn
ID: 37085924
The explanation provided by Jerry W Lewis via the link Barry provided gives us an acceptable solution.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question