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!
Same question here
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
for some reason when I try to click on the link it's telling me I dont have permissions to view that.....
From the link which is in a secure area:
What is returned is the remainder...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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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.....
Last edited by tkaplan; 02-18-2009 at 10:22 AM.
Because it returns the remainder, not what is after the decimal place.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
In VBA what I said holds true. For a worksheet formula....which I didn't understand you were using this rule applies from help:
From the vba help file on mod:Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
Run this from a module and the result is 1The 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.
[VBA]Sub a()
Dim MyResult
MyResult = 2.6 Mod 1.5
MsgBox MyResult
End Sub
[/VBA]
Mod is apparently handled differently in worksheet formula's and visual basic.....
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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...
If you want Mod(-5.2,1) = 0.2 then try
=MOD(ABS(-5.2),1)
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
From help:
=MOD(3, -2)Remainder of 3/-2. The sign is the same as divisor (-1)
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
I don't believe this is a VBA issue, but a mathematical one. Have a look here or google "Negative Mod" for more articles
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Posting Permissions
Reply With Quote
