Solved: MOD formula

[tkaplan]

I'm a bit confused on how this formula works. I thought it just takes the first number, divides it by the second, and returns whatever is after the decimal. I am plugging in numbers and not getting what I thought I would be.
for example: mod(2.6,1.5) is returning 1.1

can someone please clarify for me what MOD does?

thanks a bunch!

[mdmackillop]

Same question here (http://www.vbaexpress.com/forum/showthread.php?t=24855&highlight=remainder)

[tkaplan]

for some reason when I try to click on the link it's telling me I dont have permissions to view that.....

[lucas]

From the link which is in a secure area:


The Mod operator returns the remainder left after dividing two numbers. Each number is rounded off to a whole number before they are divided. For example: 5 Mod 2 = 1 and 5 Mod 4 = 1. After dividing, they both leave a remainder of 1.


What is returned is the remainder...

[tkaplan]

so why is mod(2.6,1.5) returning 1.1?

based on above it should be 3 divided by 2 = 1 remainder 1 so mod(2.6,1.5) shud return 1, not 1.1

<--feeling really slow here.....

[Bob Phillips]

Because it returns the remainder, not what is after the decimal place.

[tkaplan]

so I guess I'm confused about what Lucas said above - Each number is rounded off to a whole number before they are divided. - How does that work? Also running into negative number things - mod(5.2,1) = 0.2 where mod(-5.2,1) = 0.8

[lucas]

In VBA what I said holds true. For a worksheet formula....which I didn't understand you were using this rule applies from help:



Returns the remainder after number is divided by divisor. The result has the same sign as divisor.


From the vba help file on mod:


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 (javascript:hhobj_6.Click()), A (result) equals 5.


Run this from a module and the result is 1

Sub a()
Dim MyResult
MyResult = 2.6 Mod 1.5
MsgBox MyResult
End Sub

Mod is apparently handled differently in worksheet formula's and visual basic.....

[tkaplan]

thank you lucas. sorry I wasnt clear on it being a spreadsheet formula. It's interesting that it's handled differently tho.....

so now i'm just stuck on the negatives thing...

[mdmackillop]

If you want Mod(-5.2,1) = 0.2 then try
=MOD(ABS(-5.2),1)

[lucas]

From help:

=MOD(3, -2)Remainder of 3/-2. The sign is the same as divisor (-1)

[lucas]

In your post #7, I don't see why you think that is a problem.....did you think somehow the answer would be the same?

[tkaplan]

yeah. i'm still not clear as to why it's not. -5.2 divided by 1 is negative 5 remainder 0.2. +5.2 dirvided by 1 is +5 remainder 0.2......i feel like im missing something basic regarding MOD's functionality

[mdmackillop]

I don't believe this is a VBA issue, but a mathematical one. Have a look here (http://mathforum.org/library/drmath/view/52343.html) or google "Negative Mod" for more articles

[tkaplan]

thank you md. that article explained it (and now i feel stupid for not getting it earlier....)
and yeah i guess it became a mathematical question trying to figure out what mod is returning exactly....sorry about that
thanks everyone for being so patient :)