View previous topic :: View next topic 
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. 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

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. 

Back to top 


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%. 

Back to top 


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? 

Back to top 


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/ 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

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. 

Back to top 


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. 

Back to top 


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/ 

Back to top 


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. 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

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. 

Back to top 




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
