Simple Formula error

[nightcrawler23]

Hi,
why does this formula give a value error

INT(N57) & "' - "

 

[Trebor76]

It may be that the formula is set up to return either a 0 or 1 depending if the argument is FALSE or TRUE (respectively), i.e. --ISNUMBER(N57) will return 0 if the argument is FALSE, or else it will return an 1.

 

[nightcrawler23]

Well, strictly speaking, that isn't a formula. Do you mean this formula?
=INT(N57) & "' - "


What gives 1?

Peter,
N57 = 25.4
O57 = INT(N57) & "' - "

O57 gives me #Value error

when i put Isnumber(N57), it gives me 1.

I cant figure out the reason.

 

[nightcrawler23]

Just to let you know when i try it in a new workbook. everything works perfectly.
Even a new sheet in the same workbook is OK.

What is the problem with my sheet????

 

[Peter_SSs]

What does this return?
=LEN(N57)

How did the 25.4 get into N57?
- manual entry
- formula result
- vba code
- data feed
- something else?

 

[nightcrawler23]

LEN(N57) gives #VALUE error also.

actually when i check, this error comes for numbers only. Len for text cells fives the correct answer.

the value 25.4 has been copied from another workbook as values only.
The error is there even if i manually enter 25.4.

I cant understand why this abnormal behaviour for this sheet only.
even the formul N57 & "" gives me the same error.

 

[Peter_SSs]

This problem intrigued me so I asked the OP for a copy of the file.

The issue is that the 'problem sheet' has 'Transition formula evaluation' enabled.
Look in Tools|Options…|Transition tab|Sheet Options section.

If you do not require this setting for the sheet, then disable it at the above location.

If you do require this setting then instead of a formula like ..
=CONCATENATE(INT(N57),"' - ",(N57-INT(N57))*12,"''")

.. you can use something like this ..
=CONCATENATE(TEXT(INT(N57),"0"),"' - ",TEXT((N57-INT(N57))*12,"General"),"''")

Further information can be found here:
http://support.microsoft.com/kb/94202

 

[nightcrawler23]

Thanks a lot peter. Great help.