Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there any way that when I type a number into a cell, it will automatically divide that number by 100 - eg If I type 1000, it would display as 10. Perhaps using a Custom Format??

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='GillHarding' post='774653' date='11-May-2009 13:37']Is there any way that when I type a number into a cell, it will automatically divide that number by 100 - eg If I type 1000, it would display as 10. Perhaps using a Custom Format??[/quote]
    The simplest solution to this kind of problem is to type the value into one cell and have the calculation carried out in a different cell. Is there a real need to have the divide by 100 take place in the same cell?

  3. #3
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply.

    I've come up with a Custom Format to effectively divide by 1,000 (#,###,) but don't seem to be able to make it divide by 100 using this method.

    I know I could put a formula in a different column but I want to be able to type say 1000 in a cell and for it to display as 10. I've tried Paste Special, Divide which would do the trick after the event, but not as I type the number in. I've also discovered the Option for Fixed No of Decimal places in the Options menu and if I set this to 2, it seems to do what I want. However, this only seems to work on new numbers I type in, not on existing numbers.

    Hope this makes sense! If anybody can come up with a different idea, I'd be extremely grateful.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='GillHarding' post='774671' date='12-May-2009 01:49']Thanks for your reply.

    I've come up with a Custom Format to effectively divide by 1,000 (#,###,) but don't seem to be able to make it divide by 100 using this method.

    I know I could put a formula in a different column but I want to be able to type say 1000 in a cell and for it to display as 10. I've tried Paste Special, Divide which would do the trick after the event, but not as I type the number in. I've also discovered the Option for Fixed No of Decimal places in the Options menu and if I set this to 2, it seems to do what I want. However, this only seems to work on new numbers I type in, not on existing numbers.

    Hope this makes sense! If anybody can come up with a different idea, I'd be extremely grateful.[/quote]
    Hi Gill,

    Here's a way to achieve the display you're after, but it may adversely affect any calculations you're doing. That's because it actually multiplies the values by 10.

    1. Define a cutom number format as '#,' or '#,###,'
    2. Go to Tools|Options|Edit and set the 'fixed decimal places' value to '-1'

    This won't update any existing numbers. However, if you simply select a cell's value in the formula bar and press <Enter>, the contents willbe multiplied by 10 - which can be both a blessing and a curse.

    You have been warned.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    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
    Why not use copy- paste special to divide the existing numbers by 100, then use the fixed decimals to do it for new numbers. The Paste special will only have to be done once....

    Steve

  6. #6
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, you learn something new every day! I had no idea that it was possible to set the Fixed Number of Decimals to a negative number.

    Thank you both very much for your help. It's much appreciated.

    Gill

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    [quote name='GillHarding' post='774653' date='11-May-2009 12:37']Is there any way that when I type a number into a cell, it will automatically divide that number by 100 - eg If I type 1000, it would display as 10. Perhaps using a Custom Format??[/quote]


    From Excel's top panel commandbar you can use
    Tools->Options->Edit
    ..then tick the checkbox for Fixed decimal places and set the value to 2

    zeddy

Posting Permissions

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