1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Display numbers as percentage but without % character

Discussion in 'Business Applications' started by Aj_old, Nov 12, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Aj_old

    Aj_old Thread Starter

    Joined:
    Sep 24, 2007
    Messages:
    869
    Hi to everyone!

    I have 3 columns in excel. first 2 contain numers, the 3rd is the second divided by first.
    Something like this:
    A B C
    25 20 =B1/A1
    40 50 =B2/A2
    I need the numbers in the 3rd column to be formated as 0.0 if >=2 and as percentage but without % character if <2
    Is this possible:confused:
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi aj,

    You can just use an IF function...

    Code:
    =IF(B1/A1>=2,B1/A1,(B1/A1)*100)
    If you format as 0.0 you'll either return 125.0 or 2.5 or whatever the value returns. If you really don't want that extra decimal with the 'percentage' look (just know that it is not a percentage number, but 100 actually means 100 and not 1) you'll either need conditional formatting or a TEXT function, but that will cause your data to be text and not numeric. If you choose conditional formatting, you don't need the additional formula that I posted, just apply as needed right now.

    HTH
     
  3. Aj_old

    Aj_old Thread Starter

    Joined:
    Sep 24, 2007
    Messages:
    869
    Thanks but it's not what I'm looking for!
    I need to use those values late as they are.
    I need ether to multiplie all values by 100 or non!
    I thought about custom number format, but the % character should not be seen!
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    So, what values do you want to return under each condition, and what format do you want returned as well? Give an example of each (>=2 and <2).
     
  5. Aj_old

    Aj_old Thread Starter

    Joined:
    Sep 24, 2007
    Messages:
    869
    OK!
    It shoul be like this:
    2 must be shown as "2.0 times" __ but the value remains 2
    1.5 ______________ "150.0" _________________________1.5
    0.155 ____________ "15.5" __________________________0.155
    2.55 ______________"2.6 times" _____________________2.55
    I done this by using custom number format : [>=2] 0.0 "times";0.0%
    But I need the % sighn to not be displayed then value are less than 2
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Well there are issues with your percentage in having the value you want and the format you want simultaneously. I don't know of a way to combine the two using only one cell. You could with multiple cells.
     
  7. Aj_old

    Aj_old Thread Starter

    Joined:
    Sep 24, 2007
    Messages:
    869
    I was afraid it will be the answer!
    Thanks any way!
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/650897

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice