SOLVED

# [Solved] Functions with trunc() not returning true values

Copper Contributor

# [Solved] Functions with trunc() not returning true values

For example,

If cell A1 = 1.0005 then

=if(A1-trunc(A1,3)=0.0005,1,0) returns 0.

additionally if I input cell A2=0.0005 and

Cell A3= A1-trunc(A1,3)

then =A2=A3 returns FALSE.

Why does excel not recognize difference of the equation as the value 0.0005?

best response confirmed by Spogo9 (Copper Contributor)
Solution

# Re: Functions with trunc() not returning true values

If you change the formula to =A2-A3, you will see that the result is not exactly 0:

This is caused by tiny rounding errors. To avoid the problem, change the formula in A3 to

=ROUND(A1-TRUNC(A1,3),4)

(We round to 4 decimal places since your numbers have 4 significant decimal places)

1 best response

Accepted Solutions
best response confirmed by Spogo9 (Copper Contributor)
Solution

# Re: Functions with trunc() not returning true values

If you change the formula to =A2-A3, you will see that the result is not exactly 0:

This is caused by tiny rounding errors. To avoid the problem, change the formula in A3 to

=ROUND(A1-TRUNC(A1,3),4)

(We round to 4 decimal places since your numbers have 4 significant decimal places)