Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Percent number formating

    Here's a question that's been bugging me for some time: Applying a numbering format of percent to an already existing set of data creates values that are 100 times what they should be. For instance, I have a cell that has the value of 80, meaning 80 percent. If I decide that I want the percent sign as part of the formatting of the cell and click on the Percent Style button (or use the format cells dialog box), the 80 all of a sudden becomes 8000%. If I just type 80 into a cell whose format is already set for percent, 80% is what is displayed.

    I understand the mathematics of why this happens, I just don't know how to get around it without having to retype in all the numbers. Any thoughts?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Percent number formating

    Divide the numbers by 100 first.

    Enter 100 in a cell,
    select the cell,
    copy (ctrl-c),
    select the region you want to convert,
    edit -paste special - divide

    Alternately (though I would never do this) is to use the custom format:
    0"%"

    which just adds a "%" to the existing number. Mathematically this is wrong and if you use it in calcs you will get values that are 100 times too large.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Percent number formating

    I have been using your solution for a number of years, but didn't know if there was something else I didn't know--huh, ya know?

    I hadn't thought about using a custom format. This is probably the ticket for me since these numbers will not be used for calcuations. (I'm programmatically importing data into my spreadsheet and need to get the visual formatting correct.)

    If I were to need these numbers in some calculations, does anyone else have some suggestions that would keep me from having to manually divide all the numbers in the range by 100? (Although I guess I could do this programmatically, also)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Percent number formating

    If you use them in formulas just include the "/100" in the formula.

    Be aware that if you have some formatted numbers to "look like percents" (0"%") and others that are percents (0%) then you will have to keep track of which is which if you ever use them or export them. The values of the first are things like 80 with the percent tacked on, the others are true "percent" (as in "divided by 100") so are numbers like 0.80, but are displayed as 80%.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •