Opposite Function INT

%3CLINGO-SUB%20id%3D%22lingo-sub-2305537%22%20slang%3D%22en-US%22%3EOpposite%20Function%20INT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2305537%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20anyone.%20I'm%20using%20Windows%2010%20and%20Excel%20365.%20I'm%20looking%20for%20the%20function%20that%20give%20me%20the%20numbers%2C%20after%20the%20komma%20or%20digits%2C%20when%20I'm%20using%20division.%20So%20opposite%20function%20INT.%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2305537%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2305608%22%20slang%3D%22en-US%22%3ERe%3A%20Opposite%20Function%20INT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2305608%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1040352%22%20target%3D%22_blank%22%3E%40MichSimo1962%3C%2FA%3E%26nbsp%3BThat%20would%20be%20%3DMOD(cellref%2C1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2306910%22%20slang%3D%22en-US%22%3ERe%3A%20Opposite%20Function%20INT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2306910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1040352%22%20target%3D%22_blank%22%3E%40MichSimo1962%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMOD()%20is%20for%20positive%20numbers%2C%20e.g.%20%3DMOD(-1.25%2C1)%20returns%200.75.%3C%2FP%3E%0A%3CP%3EI'd%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20A1%20-%20TRUNC(A1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2306926%22%20slang%3D%22en-US%22%3ERe%3A%20Opposite%20Function%20INT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2306926%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%3E%26nbsp%3BOh%20yeah.%20forgot%20about%20that%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2308536%22%20slang%3D%22en-US%22%3ERe%3A%20Opposite%20Function%20INT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2308536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1040352%22%20target%3D%22_blank%22%3E%40MichSimo1962%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIMHO%2C%20your%20requirements%20are%20unclear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20-123.456789%20in%20A1%2C%20note%20that%20INT(A1)%2BMOD(A1%2C1)%20displays%20-123.456789%2C%20because%20%3CFONT%20color%3D%22%23FF0000%22%3EINT(A1)%20is%20-124%3C%2FFONT%3E(!)%2C%20not%20-123.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20INT(A1)%20%2B%20(A1-TRUNC(A1))%20displays%20-12%3CFONT%20color%3D%22%23FF0000%22%3E4%3C%2FFONT%3E.456789.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20you%20should%20use%20TRUNC(A1)%20instead%20of%20INT(A1)%2C%20in%20the%20first%20place%2C%20together%20with%20the%20second%20expression%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20even%20with%20positive%20123.456789%20in%20A1%2C%20neither%20MOD(A1%2C1)%20nor%20A1-TRUNC(A1)%20returns%200.456789(!).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%2C%20with%20either%20modulo%20formula%20in%20B1%2C%20%3CFONT%20color%3D%22%23FF0000%22%3EB1%3D0.456789%20returns%20FALSE%3C%2FFONT%3E(!).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(Sometimes%2C%20%22%3D%22%20works%2C%20but%20MATCH(...%2C%200)%20still%20fails.%26nbsp%3B%20That%20is%20due%20to%20an%20anomaly%20of%20how%20Excel%20%22%3D%22%20works.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20is%3A%26nbsp%3B%20those%20modulo%20formulas%20return%20the%20__binary__%20fraction%2C%20not%20the%20decimal%20fraction.%26nbsp%3B%20The%20binary%20approximation%20of%200.456789%20in%20123.456789%20has%20less%20precision%20than%20the%20binary%20approximation%20of%200.456789%20alone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20that%20matters%2C%20perhaps%20you%20should%20explicitly%20round%20the%20result%20of%20either%20modulo%20formula%20to%20the%20decimal%20precision%20that%20you%20expect%20(6%20decimal%20places%2C%20for%20my%20example).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2316197%22%20slang%3D%22en-US%22%3ERe%3A%20Opposite%20Function%20INT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2316197%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20help.%3C%2FLINGO-BODY%3E
New Contributor

Hi anyone. I'm using Windows 10 and Excel 365. I'm looking for the function that give me the numbers, after the komma or digits, when I'm using division. So opposite function INT.

Thank you in advance. 

4 Replies

@MichSimo1962 That would be =MOD(cellref,1)

@MichSimo1962 , @Riny_van_Eekelen 

MOD() is for positive numbers, e.g. =MOD(-1.25,1) returns 0.75.

I'd use

= A1 - TRUNC(A1)

 

@Sergei Baklan Oh yeah. forgot about that one.

Thanks for your help.