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

converting from normal numbers to percantages

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


Joined: 23 Apr 2006
Posts: 4

PostPosted: Sat Apr 29, 2006 4:32 pm    Post subject: converting from normal numbers to percantages Reply with quote

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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sat Apr 29, 2006 5:19 pm    Post subject: Re: converting from normal numbers to percantages Reply with quote

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
View user's profile Send private message
mr1134
Newbie
Newbie


Joined: 23 Apr 2006
Posts: 4

PostPosted: Sat Apr 29, 2006 7:27 pm    Post subject: Reply with quote

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
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Apr 30, 2006 12:17 am    Post subject: Reply with quote

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
View user's profile Send private message
9point9
Moderator
Moderator


Joined: 31 Aug 2004
Posts: 3875
Location: UK

PostPosted: Sun Apr 30, 2006 1:11 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Apr 30, 2006 4:50 am    Post subject: Reply with quote

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
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Apr 30, 2006 5:20 am    Post subject: Reply with quote

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
View user's profile Send private message
9point9
Moderator
Moderator


Joined: 31 Aug 2004
Posts: 3875
Location: UK

PostPosted: Sun Apr 30, 2006 6:35 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Apr 30, 2006 8:43 am    Post subject: Reply with quote

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
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Apr 30, 2006 2:16 pm    Post subject: Reply with quote

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