Go Back   AnandTech Forums > Software > Software for Windows

Forums
· Hardware and Technology
· CPUs and Overclocking
· Motherboards
· Video Cards and Graphics
· Memory and Storage
· Power Supplies
· Cases & Cooling
· SFF, Notebooks, Pre-Built/Barebones PCs
· Networking
· Peripherals
· General Hardware
· Highly Technical
· Computer Help
· Home Theater PCs
· Consumer Electronics
· Digital and Video Cameras
· Mobile Devices & Gadgets
· Audio/Video & Home Theater
· Software
· Software for Windows
· All Things Apple
· *nix Software
· Operating Systems
· Programming
· PC Gaming
· Console Gaming
· Distributed Computing
· Security
· Social
· Off Topic
· Politics and News
· Discussion Club
· Love and Relationships
· The Garage
· Health and Fitness
· Merchandise and Shopping
· For Sale/Trade
· Hot Deals with Free Stuff/Contests
· Black Friday 2013
· Forum Issues
· Technical Forum Issues
· Personal Forum Issues
· Suggestion Box
· Moderator Resources
· Moderator Discussions
   

Reply
 
Thread Tools
Old 11-19-2012, 09:41 PM   #1
sswingle
Diamond Member
 
sswingle's Avatar
 
Join Date: Mar 2000
Posts: 7,011
Default Weird Excel Math Issue

Ok, so I have an equation in J14, which is

=(A4+A10-A2-A8)/2

A4 is 15.15, A2 is 15.00 A10 is 25.47, A8 is 25.00

These 4 values are set as currency, 2 decimal points.

The resulting answer is 0.31. As it should be.

But then I want to take J14*1704 which should be 528.24.
However Excel is giving me 522.92

If I remove the equation from J14 and manually enter 0.31 excel gives me the proper answer of 528.24. Put the equation back in, and even though the cell still says 0.31, it gives me 522.92.

Any guesses as to what is going on?

Excel 2007 BTW
sswingle is offline   Reply With Quote
Old 11-19-2012, 10:47 PM   #2
Zorander
Senior Member
 
Join Date: Nov 2010
Posts: 999
Default

No such issue here.

One or more of those 4 fields must be more than 2 decimals. Setting them as currency simply displays them as such. It does not actually change their digit structure. Copy and paste (value) them into other cells (General format) and you should see which ones have more than 2 decimal places.
Zorander is offline   Reply With Quote
Old 11-20-2012, 06:15 AM   #3
Albatross
Platinum Member
 
Albatross's Avatar
 
Join Date: Jul 2001
Location: Romania
Posts: 2,261
Default

I think it`s a famous Excel bug with floating point multiplication,try updating Office maybe?
Or make it display more decimals places,I suspect that.31 might be .3178728582blabla or .3054124bla.

Last edited by Albatross; 11-20-2012 at 06:18 AM.
Albatross is offline   Reply With Quote
Old 11-20-2012, 12:33 PM   #4
BrightCandle
Diamond Member
 
BrightCandle's Avatar
 
Join Date: Mar 2007
Posts: 4,432
Default

There is a rule in programming that says that if you think Select is wrong then you are wrong.

Excel is definitely not getting the maths wrong, its simply impossible. I suspect the number is formatted to less decimal places or something else is off.
__________________
i7 3930k @4.4, 2xMSI GTX 680, 16GB Corsair 2133 RAM, Crucial m4 500GB, Soundblaster Z
Custom watercooled by 2x MCR 320 and 1 MCR 480
Zowie Evo CL EC2, Corsair K70, Benq XL2411T
BrightCandle is online now   Reply With Quote
Old 11-20-2012, 12:42 PM   #5
cl-scott
ASUS Support
 
Join Date: Jul 2012
Posts: 457
Default

I just tried it with Excel 2010, and got the correct answer. So there's something else going on, probably with one of your formulas.
__________________
Scott Billings
Asus Customer Loyalty
Email: cl-scott@asus.com

Opinions expressed are not necessarily those of Asus or Anandtech
cl-scott is offline   Reply With Quote
Old 11-20-2012, 03:18 PM   #6
IronWing
Lifer
 
Join Date: Jul 2001
Posts: 33,376
Default

Quote:
Originally Posted by Zorander View Post
No such issue here.

One or more of those 4 fields must be more than 2 decimals. Setting them as currency simply displays them as such. It does not actually change their digit structure. Copy and paste (value) them into other cells (General format) and you should see which ones have more than 2 decimal places.

This. (528.24-522.92)/1704 = 0.003122 which would get rounded off in a 2 decimal display.
IronWing is online now   Reply With Quote
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 07:43 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.