Home

MOD function

%3CLINGO-SUB%20id%3D%22lingo-sub-781390%22%20slang%3D%22en-US%22%3EMOD%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781390%22%20slang%3D%22en-US%22%3E%3CP%3Ewhen%20I%20use%20MOD%20I%20face%20this%20result%3C%2FP%3E%3CP%3EMOD(3%2C0.9)%3D%200.3%20Great%20result%3C%2FP%3E%3CP%3EMOD(3%2C0.2)%3D%200.2%20Wrong%20result%3C%2FP%3E%3CP%3EMOD(3%2C0.2)%20should%20be%200.00%3C%2FP%3E%3CP%3Ethis%20is%20by%20Identification%3C%2FP%3E%3CP%3Eand%20by%20the%20MS%20published%20syntax%3C%2FP%3E%3CP%3Eand%20as%20per%20GOOGLE%20sheets%20result%3C%2FP%3E%3CP%3EI%20contacted%20MS%20Support%20and%20a%20nice%20person%20gave%20me%20wrong%20answer%2C%20saying%20that%20I%20have%20to%20change%20my%20Excel%20to%20show%20results%20in%200%20digits%20instead%20of%20the%20default%202%20digits%2C%20!!!!!%20then%20the%20MOD(3%2C0.9)%3D%200.3%20will%20be%20wrong%20it%20will%20be%200%20while%20its%200.3%3C%2FP%3E%3CP%3Eclear%20MS%20Support%20Person%20tried%20just%20to%20imagine%20a%20solution%20but%20it%20went%20far%3C%2FP%3E%3CP%3Ehowever%20if%20you%20have%20an%20explanation%20let%20me%20know%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-781390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781453%22%20slang%3D%22en-US%22%3ERe%3A%20MOD%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781453%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384957%22%20target%3D%22_blank%22%3E%40AMR_BEN_KHADRA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20due%20to%20floating%20point%20error%20(%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F78113%2Ffloating-point-arithmetic-may-give-inaccurate-results-in-excel%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F78113%2Ffloating-point-arithmetic-may-give-inaccurate-results-in-excel%3C%2FA%3E).%20Using%20MOD%20with%20non-integer%20as%20second%20parameter%20is%20not%20reliable%2C%20better%20to%20use%20equivalent%20combination%20with%20INT%20or%20scale%20all%20numbers%20in%20MOD%20to%20integers.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781458%22%20slang%3D%22en-US%22%3ERe%3A%20MOD%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3Ethank%20you%2C%20that%20is%20fair%20enough%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-781603%22%20slang%3D%22en-US%22%3ERe%3A%20MOD%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-781603%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384957%22%20target%3D%22_blank%22%3E%40AMR_BEN_KHADRA%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
AMR_BEN_KHADRA
New Contributor

when I use MOD I face this result

MOD(3,0.9)= 0.3 Great result

MOD(3,0.2)= 0.2 Wrong result

MOD(3,0.2) should be 0.00

this is by Identification

and by the MS published syntax

and as per GOOGLE sheets result

I contacted MS Support and a nice person gave me wrong answer, saying that I have to change my Excel to show results in 0 digits instead of the default 2 digits, !!!!! then the MOD(3,0.9)= 0.3 will be wrong it will be 0 while its 0.3

clear MS Support Person tried just to imagine a solution but it went far

however if you have an explanation let me know 

 

3 Replies

@AMR_BEN_KHADRA 

That is due to floating point error (https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results...). Using MOD with non-integer as second parameter is not reliable, better to use equivalent combination with INT or scale all numbers in MOD to integers. 

@Sergei Baklanthank you, that is fair enough

@AMR_BEN_KHADRA , you are welcome

Related Conversations