Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rounded number format

    I need to make my piviot table totals appear in rounded thousands without changing the underlying data. So 123,456 would be formatted to show 123,000 or (123,000). If possible, correct rounding is helpful so 123,789 is 124,000 but this is not absolutely necessary. I'm looking for a custom number format that will do that. TYIA

  2. #2
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    You can use the mround formula , for eg Mround(a1,1000) eg 123485 will round to 124,000 , 123485 will round to 123000 et

  3. #3
    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
    Assuming you'd want numbers between -500 and 500 (exclusive) to appear as 0 rather than 0,000, you could use:

    [<=-500]-#,##0,",000";[>=500]-#,##0,",000";#,##0,
    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
  •