Adding text to formula changes cell format

[Gecopah]

Hello....I was hoping someone may be a able to comment on a problem I am having with displaying results in a cell. My original cell displays the results of two different formulas and one string of text.

I have simplified the formula and text to demonstrate the problem as it still occurs with only one formula and one string of text.

As an example with say cell C1 having a value entered of 5.5
If my formula of cell A1 is:

= 1 + C1

...and the number format code for cell A1 is set to 0, I get the result of 7 ( which is what I want - rounding up/down)

However if I add text to that formula, the result is such that I get the decimal result of 6.5 displayed in the cell even if the cell number format is set to 0:

= "The result is " & ( C1 + 1 )

[range]

That is because the cell will ignore the number format if it has text content.

Try...

= "The result is " & TEXT(( C1 + 1 );"0")

[Villeroy]

You must not believe that any format attribute has any influence on your cell value. The cell value remains 6.5 even when you format that number as date+time which gives January 5th 12:00PM or percent (650%), currency, fraction or whatever. It remains 6.5.
If you want 7 then use ROUNDUP(6.5) and the cell value will be 7.

A text value does not have any number format at all and concatenation always gives text. Concatenated numbers are converted to plain unformatted decimal. Function TEXT converts a number to text to be displayed in a given number format.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Gecopah]

Thank you Villeroy and range...both suggestions work for me but I went with ranges since this will automatically round up or down; which ever one it is closer too.

[range]

As Villeroy says, formatting only changes how a cell displays its contents, if you use that number cell again it will have a value of 6.5 even though it displays 7.

[Gecopah]

Thanks range...yeah..that actually works out perfectly that way for my purposes.