Back in my C programming days I thought there was a constant or function (I can't remember what it was called, let's call it SMALL) whose value was the smallest possible positive value. (Imagine the LSB=1 and all other bits = 0.) It was used to avoid a divide by zero error.

For example, let's say you want to compute z = x/y. Then you could calculate
z = x / (y + SMALL);

Is there a built-in function in MS EXCEL that implements SMALL? For that matter, does it exist in C/C++ or am I going crazy?

  • I've never heard of this, and it sounds like a terrible way to avoid a divide by zero error. The small price of an if statement is nothing compared to knowingly making all your calculations less accurate. – Ryathal Feb 23 '12 at 16:48
  • 15
    "It was used to avoid a divide by zero error." That has EPIC FAIL written all over it. – S.Lott Feb 23 '12 at 16:52
  • 3
    What if y == -SMALL? As others have said, using this to avoid division by zero is a horrifically bad idea, and is a good sign that you need to rethink what you're doing. – user11946 Feb 23 '12 at 18:09
  • In my case x and y are both nonnegative. z is a percentage and is rounded. – Stainsor Feb 24 '12 at 14:28
  • Just so you all know, in most modern physics engines this is how avoiding division by zero is handled. It might seem stupid, but you can't have an IF around every sqrt(0.f) in a real-time application. – Ben DeMott Sep 4 '16 at 22:29

Take a look at http://www.cplusplus.com/reference/clibrary/cfloat/ the FLT_EPSILON can be used to compare a float variable to 0 (zero), or nearly zero in that case.

There's an other article here: http://www.cygnus-software.com/papers/comparingfloats/Comparing%20floating%20point%20numbers.htm

EDIT: what you need is this q&a: https://stackoverflow.com/questions/9380108/get-machine-epsilon-in-microsoft-excel it describes the epsilon value in excel.

  • Yes, that q&a answers it. I guess "=2^-1022" is the simplest way. – Stainsor Feb 24 '12 at 15:31

I think you're probably remembering FLT_EPSILON, DBL_EPSILON, and so on.

They aren't quite what you're describing though: they're not the smallest number greater than 0. Rather, they're the smallest number greater than 1. For better or worse, however, DBL_EPSILON-1 won't be even close to the smallest number greater than 0. Epsilon is really intended to be scaled by multiplication, not addition or subtraction.

This is not normally used to avoid problems with division by zero, but to give some idea of the smallest possible difference between two numbers, and whether two numbers are "substantially different", or any inequality between them is really due to rounding errors (and such).

I'd also note that replacing x/y with x/(y+EPSILON) could quite easily cause a division by zero rather than preventing it (i.e., if y == -EPSILON).

  • Excellent answer. I think I was remembering EPSILON, but no one really ever told me how to use it. So Thanks! – Stainsor Feb 24 '12 at 15:29

The proper C++ way is in the <limits> header:

in particular std::numeric_limits<T>::epsilon() is defined (specialized) for all the built-in types as in cppreference

  • great example at that link showing the scaling method Jerry referred to – Stainsor Feb 24 '12 at 15:32

As @Rythal said its a terrible way of avoiding divide by zero, however, if you wish to persist, the minimum (and maximum) values for float and double are defined in float.h. This is explored here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.