[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
mr1134
Newbie

Joined: 23 Apr 2006
Posts: 4

 Posted: Sat Apr 29, 2006 4:32 pm    Post subject: converting from normal numbers to percantages i got a formatting problem. when i convert a cell from number format to a percantage format i get for an 4,5 a 4500,0% while a 4,5 should be a 4,5%. this can't be that hard, but i still didn't figure out how to solve it.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Sat Apr 29, 2006 5:19 pm    Post subject: Re: converting from normal numbers to percantages

 mr1134 wrote: i got a formatting problem. when i convert a cell from number format to a percantage format i get for an 4,5 a 4500,0% while a 4,5 should be a 4,5%. this can't be that hard, but i still didn't figure out how to solve it.

It's worse. [I have the English version, so a decimal is "." not "," here]

If I enter 4.5 I do see 4.50%. If I do a calculated entry, the result makes no sense [to me.] I format the cell as percentage, then enter 12/30 expecting then a percentage format of the fraction [0.4 ,seen as 40.00%], and see this: 1129300.00%. If I instead use data from other cells, D1/E1, where D1 has 12, and E1 has 30, I do see the 40.00%, as I should.

Now I've been known to take some pride in being able to do mathematics in the past, but I see no reason in this result. All I can think of is that perhaps the fraction is being translated as a date, and that the internal date representation value is then appearing, formatted as a percentage. That's just a wild guess though, and I've been known to overlook something before.

David.
mr1134
Newbie

Joined: 23 Apr 2006
Posts: 4

 Posted: Sat Apr 29, 2006 7:27 pm    Post subject: could be my problem. i'm also working with the english version, so it might be confused with the ",". if i enter a 4,5 into a field that is already formatted as percent it also stays a 4,5. but when i enter it in a cell formatted as number and than convert that cell to percentages, i get as i already told a 4500,0%.
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Sun Apr 30, 2006 12:17 am    Post subject: Some suggestions, (all based on . as the UK decimal separator): 1. Always enter a % as "10%" and Calc will realise that you want a percent, so behind the scenes the actual number is 0.1 (because 10% = 10/100 = 0.1) but it shows 10% in the formula bar and on the screen. 2. If you enter the number 10, then format as percentage, it wil show as 1000%. Similarly if you enter 1, then format as percentage, it wil show as 100%. This is correct mathematically. 3. If you enter 12/30 (without an equals sign) it will think you want a date, defaulting to the current year. If you've already formatted as percentage, it wil put the number of days since 1904 or whenever, i.e. several thousand, and then show as a percentage, so a silly number, 3908100%. 4. If you enter =12/30 (with the equals sign) it realises this is a mathematical expression, so shows 40%, if already formatted as percentage. 5. Percent is only a format. Put the number 0.5 in three cells. Format 1 cell as percent, so it shows 50%. Format another as fraction, so it shows as 1/2. Leave the other as 0.5. It's same number behind the scenes in all 3 cells, it's just the format that's different. Does this help, have I just caused more confusion?
9point9
Moderator

Joined: 31 Aug 2004
Posts: 3875
Location: UK

 Posted: Sun Apr 30, 2006 1:11 am    Post subject: This is just how percentages work, I know they confuse some people. 1 = 100% You can prove this because x/x=100% or 1. 1*100 != 100% it equals 10000% I would always specify a percentage sign._________________Arch Linux OOo 3.2.0 OOoSVN, change control for OOo documents: http://sourceforge.net/projects/ooosvn/
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Sun Apr 30, 2006 4:50 am    Post subject:

 RickRandom wrote: 4. If you enter =12/30 (with the equals sign) it realises this is a mathematical expression, so shows 40%, if already formatted as percentag

I would have hoped that the purpose of formatting a cell as a percentage would be to convert any entered or calculated value to an expression of percent.

Then entering 12/30, which has a value of 0.4, should be seen visually as 40.00% [default 2 decimals.] It is not. It is seen as 12/30%, to the left of the cell, so possibly as text? I see from your note [thanks] that the expression must be preceded by an = sign, otherwise it is thought of as a date.

The problem is one of consistent expectation. Something new every day.

David.
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Sun Apr 30, 2006 5:20 am    Post subject: To me, formatting shouldn't convert the actual number, only how it is displayed. I suggest: When entering a fixed percentage that is not a calculation, type 40%, using the percent symbol. When overwriting it, don't type the % again, just type 30 if you want 30%. When entering a calculation that you want to show as a percentage, just do the calculation, e.g. =12/30 without a % symbol, enter that, then set the format to percent. As a numbers nerd, I find it easy to get a spreadsheet to do the percentages how I want them, but it doesn't mean it's easy to explain to someone else.
9point9
Moderator

Joined: 31 Aug 2004
Posts: 3875
Location: UK

Posted: Sun Apr 30, 2006 6:35 am    Post subject:

 RickRandom wrote: To me, formatting shouldn't convert the actual number, only how it is displayed.

It doesn't convert the number as such. 1 = 100% under all circumstances, there is no form of conversion to do that. 1 x 100 != 100%.

From what I've seen this is a standard thing for any spreadsheet to do.
_________________
Arch Linux
OOo 3.2.0

OOoSVN, change control for OOo documents:
http://sourceforge.net/projects/ooosvn/
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

Posted: Sun Apr 30, 2006 8:43 am    Post subject:

 9point9 wrote: [It doesn't convert the number as such. 1 = 100% under all circumstances, there is no form of conversion to do that. 1 x 100 != 100%. From what I've seen this is a standard thing for any spreadsheet to do.

I concur. I wasn't suggesting it does convert, it was just that David had said

I would have hoped that the purpose of formatting a cell as a percentage would be to convert any entered or calculated value to an expression of percent.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Sun Apr 30, 2006 2:16 pm    Post subject:

 RickRandom wrote: it was just that David had said I would have hoped that the purpose of formatting a cell as a percentage would be to convert any entered or calculated value to an expression of percent.

What David meant was that you can "convert" from a fraction to a decimal to a percentage and vice versa. All have the same value, of course. [I used to teach the stuff and well beyond, and still love to solve math problems. Check out "The pirate problem." from George Gamow.] My expectation was that a fraction entered as 12/30 would be recognised as such. My error was in not preceding it with an = sign.

David.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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