Getting excel to return a zero instead of gobbledygook

**J.McQ** · 12-20-2019, 07:03 AM

Does anybody know if there's a setting somewhere that stops excel producing the likes of 2.182737657735E-13 instead of a zero? I can convert to number to display a zero, but it's still all that gobbledygook in the cell and it's interfering with my formatting when I want something to happen based upon a cell having a zero value.

**MrShorty** · 12-20-2019, 07:12 AM

The "gobbledygook" as you call it is a result of "floating point error" which is an inherent part of the way computer's do calculations. Here are some links discussing floating point error: https://www.excelforum.com/groups/ma...nd-errors.html

The usual solution is to nest the existing function inside of an appropriate rounding function. Something like =ROUND(existing function, expected precision).