Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mod with Doubles

Am I doing something wrong or does the VBA Mod operator actually not work with floating point values like Doubles?

So I've always sort of assumed that the VBA Mod operator would work with Doubles based on the VB documentation, but in trying to figure out why my rounding function doesn't work, I found some unexpected Mod behavior.

Here is my code:

Public Function RoundUp(num As Double, Optional nearest As Double = 1)
    RoundUp = ((num \ nearest) - ((num Mod nearest) > 0)) * nearest
End Function

RoundUp(12.34) returns 12 instead of 13 so I dug a little deeper and found that:

12.5 Mod 1 returns 0 with the return type of Long, whereas I had expected 0.5 with a type of Double.


Conclusion

As @ckuhn203 points out in his answer, according to the VBA specification,

The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result.

And

Usually, the data type of result is a Byte, Byte variant, Integer, Integer variant, Long, or Variant containing a Long, regardless of whether or not result is a whole number. Any fractional portion is truncated.

For my purposes, I need a floating point modulo and so I have decided to use the following:

Public Function FMod(a As Double, b As Double) As Double
    FMod = a - Fix(a / b) * b

    'http://en.wikipedia.org/wiki/Machine_epsilon
    'Unfortunately, this function can only be accurate when `a / b` is outside [-2.22E-16,+2.22E-16]
    'Without this correction, FMod(.66, .06) = 5.55111512312578E-17 when it should be 0
    If FMod >= -2 ^ -52 And FMod <= 2 ^ -52 Then '+/- 2.22E-16
        FMod = 0
    End If
End Function

Here are some examples:

FMod(12.5, 1) = 0.5 FMod(5.3, 2) = 1.3 FMod(18.5, 4.2) = 1.7

Using this in my rounding function solves my particular issue.

like image 429
Blackhawk Avatar asked May 08 '14 18:05

Blackhawk


People also ask

Can you use mod with doubles?

Double Modulus Operator If either or both operands of the mod operator have type double, then evaluating it produces the remainder. This kind of mod operator does not exist in C or C++ where the mod operator only works with int operands. The evaluated result is a double value.

Can you mod a double in Java?

In Java, there is no modulus operator. Instead, % is the remainder operator.

Can you use modulus with floats?

Yes, %(modulo) operator isn't work with floats and double.. if you want to do the modulo operation on large number you can check long long int(64bits) might this help you.

What types does the mod (%) work on?

3) modulus operator is not just applicable to integral types e.g. byte, short, int, long but also to floating-point types like float and double. 4) You can also use the remainder operator to check if a number is even or odd, or if a year is leap year.


Video Answer


2 Answers

As a work around your can do some simple math on the values. To get two decimal of precision just multiply the input values by 100 and then divide the result by 100.

result = (123.45*100 Mod 1*100)/100
result = (12345 Mod 100)/100
result = 0.45

I'm late to the party, but just incase this answer is still helpful to someone.

like image 113
Russell Munro Avatar answered Sep 28 '22 11:09

Russell Munro


According to the VB6/VBA documentation

The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result. For example, in the following expression, A (result) equals 5. A = 19 Mod 6.7 Usually, the data type of result is a Byte, Byte variant, Integer, Integer variant, Long, or Variant containing a Long, regardless of whether or not result is a whole number. Any fractional portion is truncated. However, if any expression is Null, result is Null. Any expression that is Empty is treated as 0.

Remember, mod returns the remainder of the division. Any integer mod 1 = 0.

debug.print 12 mod 1 
'12/1 = 12 r 0

The real culprit here though is that vba truncates (rounds down) the double to an integer before performing the modulo.

?13 mod 10
 '==>3 
?12.5 mod 10
 '==>2 

debug.print 12.5 mod 1
'vba truncates 12.5 to 12
debug.print 12 mod 1
'==> 0
like image 25
RubberDuck Avatar answered Sep 28 '22 10:09

RubberDuck