OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Adding text to formula changes cell format

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Gecopah
Newbie
Newbie


Joined: 06 Mar 2012
Posts: 3

PostPosted: Tue Mar 06, 2012 9:20 am    Post subject: Adding text to formula changes cell format Reply with quote

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 )
Back to top
View user's profile Send private message
range
General User
General User


Joined: 04 Jan 2012
Posts: 21

PostPosted: Tue Mar 06, 2012 9:29 am    Post subject: Reply with quote

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

Try...

= "The result is " & TEXT(( C1 + 1 );"0")
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Mar 06, 2012 9:35 am    Post subject: Reply with quote

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 https://forum.openoffice.org


Last edited by Villeroy on Tue Mar 06, 2012 1:51 pm; edited 1 time in total
Back to top
View user's profile Send private message
Gecopah
Newbie
Newbie


Joined: 06 Mar 2012
Posts: 3

PostPosted: Tue Mar 06, 2012 9:42 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
range
General User
General User


Joined: 04 Jan 2012
Posts: 21

PostPosted: Tue Mar 06, 2012 11:57 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Gecopah
Newbie
Newbie


Joined: 06 Mar 2012
Posts: 3

PostPosted: Tue Mar 06, 2012 1:53 pm    Post subject: Reply with quote

Thanks range...yeah..that actually works out perfectly that way for my purposes.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group