Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post
    Hi,

    I'm sure this will have been answered before but I can't find the relevant thread.

    I want whole numbers displayed without a decimal point and decimals displayed with a decimal point, but only to two decimal places.

    I apologise in advance, because I'm sure it's an obvious solution!

    Jim MacLeod
    Shetland Isles

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Which version of Excel?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post
    Sorry, I should have said - 2007

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Then you're in luck:

    Format the cells as normal to display 2DP. Then add a conditional format using the formula =MOD(A1,1)=0 and apply a number format with no DP.

    Note: the A1 is a reference to the active cell at the time you add the CF - adjust as required.

    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Shetland Isles, Shetland, Scotland
    Posts
    154
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thank you!

    I thought I was missing something in custom formatting.

    Surely a future office development would be to add something like [.] to custom format syntax that gives a conditional decimal point?

    Thanks again,
    Jim

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    They're too busy making charts look pretty and adding enterprise features...
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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