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

Number Formats and Locale Settings
Goto page Previous  1, 2, 3, 4  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Wed Jul 11, 2007 12:16 am    Post subject: Reply with quote

That's because it's not being recognised as a number. It is being treated as text.

To enter a fraction in Calc you need to specifically format the cell as a fraction, AND enter the fraction in improper (top heavy) form. ie. 49/2 in your example.
Back to top
View user's profile Send private message
mfa-oo
Newbie
Newbie


Joined: 10 Jul 2007
Posts: 2

PostPosted: Wed Jul 11, 2007 3:43 am    Post subject: Reply with quote

Thanks; I had guessed the reason, but not the solution. Probably easier in general to enter a formula to evaluate the fraction, especially where it isn't a simple one -- e.g., =24+17/47.
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Fri Aug 24, 2007 5:06 am    Post subject: Reply with quote

I need a bit more help understanding formatting...
  • Open a new spreadsheet with no formatting. ie. Default cell style is set to Number General and all cells are Default cell style.

  • Enter something that Calc will "interpret". eg 1.2.3, which on my locale is immediately formatted by Calc to 01/02/03, blue with Value Highlighting on; or 123 which is formatted as a number, right-aligned, blue with Value Highlighting on.

  • Edit the Default cell style from Number General to Text @.

  • Reset all cells to Default cell style - date changes to 37653 but stays blue, and 123 stays blue.
Why aren't numbers converted to text by the format change (I appreciate this would probably be bad, but would like to understand better)?

I can input text to number formatted cells with an apostrophe. Why is there no way numbers can be input to text formatted cells? (Unless I paste a number from another spreadsheet, which brings its number formatting with it).

Is there a way to access and change the underlying "type" that Calc must store for the contents of each cell?
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 Aug 28, 2007 2:05 am    Post subject: Reply with quote

Direct input, find&replace and several kinds of import seem to be the only ways to change data. Whatever you do with formatting, view or Menu:Tools>Options... may change the representation of data, but never ever the data themselves. I'm conviced that this simple rule is a feature indeed.
We can convert between text and number in place by changing the values through find/replace http://www.oooforum.org/forum/viewtopic.phtml?t=57743
Another approach involves functions VALUE and TEXT.
Back to top
View user's profile Send private message
magnoliasouth
General User
General User


Joined: 03 Jan 2007
Posts: 17

PostPosted: Thu Oct 04, 2007 9:34 am    Post subject: Auto format and formatting issue by rows. Reply with quote

In Excel (gag, I know), you can format row backgrounds to stay a certain background color, despite sorting.

For example say this is my sheet:
1 Oranges (grey background)
2 Apples (white background)
3 Bananas (grey background)

I then decide to sort text from A to Z, but the colors remain:
1 Apples (grey background)
2 Bananas (white background)
3 Oranges (grey background)

As I mentioned, this can easily be done in Excel (http://www.cpearson.com/excel/banding.htm), but I cannot figure out how to do this in OpenOffice. I have applied a style, but it's not showing up. Can it be done?
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Thu Oct 04, 2007 9:49 am    Post subject: Re: Auto format and formatting issue by rows. Reply with quote

magnoliasouth wrote:
In Excel (gag, I know), you can format row backgrounds to stay a certain background color, despite sorting.

For example say this is my sheet:
1 Oranges (grey background)
2 Apples (white background)
3 Bananas (grey background)

I then decide to sort text from A to Z, but the colors remain:
1 Apples (grey background)
2 Bananas (white background)
3 Oranges (grey background)

As I mentioned, this can easily be done in Excel (http://www.cpearson.com/excel/banding.htm), but I cannot figure out how to do this in OpenOffice. I have applied a style, but it's not showing up. Can it be done?


Yes.

Don't use the sort icon in the tool bar. Use Data > Sort and it should keep the formatting together. Data >Sort also allows you to sort by headings which the icons don't.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Fri Oct 05, 2007 12:16 am    Post subject: Reply with quote

Data > Sort... > Options > Include formats
Back to top
View user's profile Send private message
magnoliasouth
General User
General User


Joined: 03 Jan 2007
Posts: 17

PostPosted: Fri Oct 05, 2007 5:09 am    Post subject: Reply with quote

Aha! Thank you so much to the both of you. Very Happy
Back to top
View user's profile Send private message
yunluck
General User
General User


Joined: 26 Dec 2007
Posts: 8

PostPosted: Wed Dec 26, 2007 6:03 am    Post subject: Reply with quote

I found the OOo Calc is hard to display a spreadsheet which is done by a MS excel.It are always error code.I am from China.How to deal with that?
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Fri Dec 28, 2007 11:24 am    Post subject: Reply with quote

yunluck wrote:
I found the OOo Calc is hard to display a spreadsheet which is done by a MS excel.It are always error code.I am from China.How to deal with that?


You are not supplying us with enough information.

I would suggest that you start a new thread in the Calc forum with some details of your system and descriptions of what the error codes are and where in the sheet they are appearing

Some basic questions
What OS and version of OOo are you using?
What version of Exce was used )
What are the error codes (copy exactly if possible)
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Alan2Po
Newbie
Newbie


Joined: 30 Mar 2008
Posts: 3

PostPosted: Sun Mar 30, 2008 8:14 am    Post subject: decimal symbol Reply with quote

Hello.

I red posts about decimal. But got no answer.

I have set decimal symbol to "." (point) in windows regional and language options.

in calc pressing decimal symbol on keypad causes "," (comma) to appear.
in other programs pressing this button causes decimal symbol (point) to appear.

I tried to change thru language options this, but no success, comma keep coming.

is it possible for XP with Latvian locale change decimal to "." for calc ?
and how?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Mar 30, 2008 8:40 am    Post subject: Reply with quote

No, Latvian locale implies comma, no matter how you tweaked your operating system's Latvian locale.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
JJJoseph
Power User
Power User


Joined: 25 Sep 2005
Posts: 81

PostPosted: Tue Apr 01, 2008 1:17 pm    Post subject: Calendar errors Reply with quote

One of my biggest problems with the 'smart' calendar dates is when pasting text into a spreadsheet. This is easy to do with Excel, but impossible with OO Calc. Calc will change everything that looks like a date, whether it is or not. If it is a date, Calc will change it (incorrectly).

Is there any way I can stabilize the calendar dates so that they remain as text, regardless of what Calc wants to see? I'd like to have English language, America decimals, and ANSI ates (YYYY.MM.DD)., and letter-size (not A4) printing. Dates without punctuation (YYYYMMDD) are acceptable, but dates with slash separators are difficult to type (decimal separators are much faster). If I could turn off the OO smart dates, I could work much faster just using text dates with decimal separator.

Using ANSI dates removes all ambiguity, so there's no need to change the date display when viewing in different locales. Therefore the text solution is the best one, yet I can't figure out how to do it consistently in Calc.

The ideal total solution would be "locale=none" (with dates as text-only) so that the locale variables could be managed by the user.
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 Apr 01, 2008 3:39 pm    Post subject: Reply with quote

Text is text in any spreadsheet program. Number formatting and number format locales has no effect on text values. If do not want to do any calculations with your dates and always want to type all the digits in "20081231", then simply use text values. Paste special "unformatted text" allows you to specify the import type for each column. Preformatting the target cells works as well.
However, I would accept numeric dates and simply apply my own number format, preferably through styles.
Quote:
This is easy to do with Excel, but impossible with OO Calc.
Do you believe that any spreadsheet program other than Excel can be acceptable for you?
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
JJJoseph
Power User
Power User


Joined: 25 Sep 2005
Posts: 81

PostPosted: Thu Apr 03, 2008 8:43 pm    Post subject: OO date bugs Reply with quote

I just noticed that, with locale="Canada", the only date format acceptable to the data input window is mm-dd-yyyy, which is incorrect for Canada. Canada officially uses the ISO8601 format (yyyy-mm-dd), and there doesn't seem to be any way to correct this bug. I also note that Germany, which also officially uses ISO8601 format, has the same bug. I guess the OO designers don't travel much!

BTW: sure, I really like Excel, but we're trying to adapt to OO because of the cost savings, so if you can help us solve these bugs, we'll be another bunch of happy OO users Smile
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
Goto page Previous  1, 2, 3, 4  Next
Page 2 of 4

 
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