Excel decimal bug!

**thompssc** · 05-29-2012, 03:26 PM

Hello, I'm at work and my boss seems to have run into a unique bug that I have never encountered. While computing price differences between two lists, we have found that excel adds a value in the fifteenth decimal place, but only in certain instances. This is obviously a small amount, but when it's being multiplied by thousands of units per part in a 40,000 part catalog, it starts to create problems with our enterprise software. Attached are screenshots showing the values as well as the formulas (forgot to include the row labels in the pics, just take it on faith that I'm not stupid enough to reference the wrong cells...). Is this some known bug? Is there a fix?
decimal_error_formulas.jpg decimal_error.jpg

**daddylonglegs** · 05-29-2012, 03:29 PM

Yes, this is a known error caused by the "floating point arithmetic" that Excel uses. You probably need to use the ROUND function in your calculations, see here

**Russell Dawson** · 05-29-2012, 03:34 PM

Hi

You need to set precision as displayed through the Excel Options.

Office Button < Excel Options < Advanced < "When calculating this workbook"-check "Set precision as displayed"

**shg** · 05-29-2012, 03:40 PM

I'd be very reluctant to use Precision as Displayed without thinking through all the possible unintended consequences. Selective rounding is generally a much safer approach.