Prevent Text formula from rounding off

[AthmanArumugam]

Hi All,

I have values in two cells which needs to be compared.
Values in the cells are floating point decimals.
Eg. Value in Cell A1 = 123645.445
Value in Cell A2 = 123645.446

I use Text formula to do the comparison. "=Text(A1,"0.0")=Text(A2,"0.0")".
The result is false. Actually I want the first value after the decimal only to be compared.
But the Text formula rounds off the value in cell A1 to the nearest possible value.
If converts A1 to 123645.5 and compares with A2[123645.4]. Not sure why
only A1 value is rounded off while not A2.

Kindly refer to cell Q12 which I am talking about.

 

[shrivallabha]

Maybe you should use ROUNDDOWN formula and see if it fits the bill.
ROUNDDOWN(A1,1)=ROUNDDOWN(A2,1)

 

[NARAYANK991]

Hi ,

Excel is doing correctly what ever it is supposed to do.

Remember that what you see in a cell is not what is in it ; cell formats can display anything.

The formula you are using in Q12 is :

=TEXT(B12,"0.0")=TEXT(M12,"0.0")

B12 is a user entered value , and is : 1324599997.45

M12 is a formula , and the result of the formula can be seen by pressing F9 with the cursor placed in the cell ; this shows : 1324599997.44618

Excel is trying to round off to the number of decimal places specified in the TEXT function ; if you change your formula to :

=TEXT(B12,"0.00")=TEXT(M12,"0.00")

Excel now rounds off both values to 2 decimal places , and returns the result TRUE.

The best way to avoid such unexpected results is to use the TRUNC function , which will always do what you expect it to do viz. remove the unwanted decimal places.

Thus =TRUNC(1.99999999999999,1) will return 1.9

Narayan