function to leave fractional (decimal) part

[annfos]

I need a way to "truncate" a number by removing integer part and
leaving decimal. Something opposite to function TRUNC. I hope someone
helps me.

 

[Dave Peterson]

Maybe:

=a1-int(A1)
or
=mod(a1,1)

I need a way to "truncate" a number by removing integer part and
leaving decimal. Something opposite to function TRUNC. I hope someone
helps me.

 

[JE McGimpsey]

One way:

=MOD(A1,1)

 

[Harald Staff]

=MOD(A1,1)

HTH. Best wishes Harald

 

[annfos]

Wow! it worked! Thank you guys! I love you!

 

[Dana DeLouis]

Just keep in mind that Microsoft doesn't want to fix the bug...I mean
"feature".

=MOD(134217728.3,1)

returns the #NUM! error.

However, one number less...

=MOD(134217727.3,1)
returns 0.3 as expected.

http://support.microsoft.com/default.aspx?scid=kb;en-us;119083

HTH :>)

 

[annfos]

Oh!! I still can't do what I want! here's what happens now:
=if(mod(A1;1)=0.05;.... doesn't return TRUE when it's actually true!

 

[Dave Peterson]

It could be a rounding error -- or it could be that the actual value in A1 isn't
what you see (hidden by formatting (two decimals maybe????).

You could look for a little rounding error with something like:

=if(ABS(MOD(A1,1)-0.05)<0.0000000001,"close enough","not too close")

Change that 0.0000000001 to as close as you need.

 

[Jerry W. Lewis]

Try =IF(A1=INT(A1)+0.05;...) to minimize binary/decimal conversion issues.

Most exact decimal fractions (including 0.05) have no exact binary
representation and hence must be approximated (just as 1/3 must be
approximated in decimal. To illustrate, think of a hypothetical
calculator that carries 4 decimal figures, then 4/3 would be 1.333 and
1/3 would be 0.3333. Your calculation would then be like
IF((1.333-1)=0.3333;...) = IF(0.333=0.3333;...)
which correctly identifies a difference.

Jerry