Results 1 to 8 of 8

Thread: Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

    So, I'm using custom formatting to show -$1,200,000 as -$1.2M, but Excel is showing it as -$1200K.

    Here's the custom formatting that I'm using:
    [<999950]$0,"K";[<999950000]$0.0,,"M";$0.0,,,"B"

    The weird thing is that when I do a positive number, like $1,300,000, it shows up as $1.3M, which is what I want.

    Negative numbers are the only one's affected.

    Does anyone know how to fix this?

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,795
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

    Any negative number is less than 999950, so your first format will apply. You will have to use a conditional format to apply separate number formats based on the values, as you can only have 2 condition sections in a custom number format.

  3. #3
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,744
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

    That's because a negative number is less than 999950. Custom number formats are limited to four zones (typically positive, negative, zero and text) so you won't be able to achieve what you're trying to do.
    I'd suggest using the TEXT function in a helper column, or using VBA to modify the format of each cell.
    Neil

  4. #4
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

    Thanks guys! You wouldn't happen to know the conditional formatting formula that would make that happen or some other way to accomplish what I'm trying to do, would you?

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,795
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

    Apply a CF using Cell Value is < 0 and use that custom number format but reversing the two conditions and negating both values.

  6. #6
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

    Quote Originally Posted by RoryA View Post
    Apply a CF using Cell Value is < 0 and use that custom number format but reversing the two conditions and negating both values.
    That worked for formatting the number, but the chart that I have built off of that number changed to the un-abbreviated number. Is there a way to fix that?

  7. #7
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

    I figured it out. Had to change the number format to custom in the chart

  8. #8
    Board Regular
    Join Date
    May 2014
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Abbreviated formatting of numbers in Excel - Negative numbers not right - custom formatting

    Thanks for your help!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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