Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #5  
Old 06-04-2014, 11:24 AM
BobBridges's Avatar
BobBridges BobBridges is offline Rounding values inconsistent Windows 7 64bit Rounding values inconsistent Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I agree with your diagnosis, required—it has to be a rounding issue—and with gebobs' suggestion. But I'll expand on it a bit in case there's still confusion: As a matter of picky terminology, I would say that the value in C9 is accurate, just not precise.

Take an example:
Code:
3.14159265359
2.71828182846
2.99792458000
1.41421356237
Their precise sum is 10.27201262442, but unless you tell Excel to use special formatting all it'll show you is 10.27201262. That answer is accurate, it's just not precise to all 13 significant digits.

Now tell Excel to display them to just one decimal point, like this:
Code:
3.1
2.7
3.0
1.4
If these were the numbers' true values, the total should be 10.2, but in fact Excel claims their sum is 10.3—and Excel is correct. When you display them using just one significant digit, the display says 3.1 but the value behind the scenes, the one that's being summed, is still 3.14159-etc.

What do you do about it? Well, it depends on your needs. It would probably be more accurate for many purposes to leave it as it is, giving 10.3 as the sum rather than 10.2. But if you need to for some reason, you can round the individual numbers to their truncated form, and the sum of those values (rather than the real ones) will come out to 10.2 as you expected.

Extra credit for identifying the sample numbers above.
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Rounding values inconsistent Inconsistent Formatting YEG_V Word Tables 3 05-08-2013 03:06 PM
Custom formatting code - rounding problem venkys4u Excel 1 08-14-2012 07:45 PM
Rounding values inconsistent Standard Dev. Rounding - Excel judicial85 Excel 1 08-28-2011 05:41 PM
Inconsistent Margins judyfogarty Word 0 08-01-2010 02:54 AM
Rounding values inconsistent Federal financial rounding? markg2 Excel 6 05-26-2010 09:54 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:12 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2026, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2026 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft