Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Millions & Thousands Custom Number Formatting

This is a discussion on Millions & Thousands Custom Number Formatting within the Excel Questions forums, part of the Question Forums category; To display 1,100,000 as $1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting. [>=1000000] $#,##0.0,,"M";[ This works ...

  1. #1
    New Member
    Join Date
    Feb 2008
    Posts
    16

    Default Millions & Thousands Custom Number Formatting

    To display 1,100,000 as $1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting.

    [>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General

    This works great... unless the number goes negative. For instance a negative million will show as a negative thousand K. I've tried many different variations to attempt the negatives to appear correctly however the initial order Excel views them (positive; negative; zero; text) makes them all invalid.

    I suppose ideally what I'm asking is if you can combine Custom Number Format much like a AND OR function? Something to the effect of...

    positive millions "M" or thousands "K"; negative millions "M" or thousands "K"; general

    ... because I would want to format for either/ or millions or thousands at the same time??

    I hope this makes sense. Does anyone know of a way to do this? Or better yet... an alternative to my insanity that Excel will accept?

    Many thanks,
    Kudos

  2. #2
    Board Regular
    Join Date
    Jan 2003
    Location
    CA, Bay Area
    Posts
    2,062

    Default Re: Millions & Thousands Custom Number Formatting

    the positive part of the format is formatted for Millions. The negative part for Thousands. You can't have both mixed like that.

    also, note what you are doing here... two commas before "M" and one comma before K. That's correct for for thousands but you have to remain consistent.
    [>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General

    There is no magic to "M" & "K" They are merely textual references unrelated to real values.
    Last edited by nbrcrunch; Feb 24th, 2009 at 11:11 AM.
    Give a man a fish & you've fed him once. Teach a man to fish & you feed him for a lifetime. Give a man a fish a day & he becomes a spoiled adult still living with his parents at 35.

  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Millions & Thousands Custom Number Formatting

    Hi and welcome to the board!!
    Try changing your 2nd condition.
    Code:
    [>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";General
    lenze
    Edit: you might want to color your negatives Red
    Code:
    [>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";[Red]General
    Edit2: How are the numbers obtained? By formula, a Query or user entry?
    Last edited by lenze; Feb 24th, 2009 at 11:18 AM.
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Millions & Thousands Custom Number Formatting

    After rereading your post, another question comes to mind. How do you want your negatives formatted? If you break them at > [-1000000] and < [1000000], that would be four conditions and you will need to use code.

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  5. #5
    New Member
    Join Date
    Feb 2008
    Posts
    16

    Default Re: Millions & Thousands Custom Number Formatting

    Right, I realize the millions are in positive and thousands in negative. This was a formula that a contractor obtained for us and we've been using since. I'd like to correct the coding that they had done and in turn make the formatting useful for positives and negatives if possible.

    Thanks Lenze! When I try that however it wouldn't format the thousand with a K at the end. The numbers are obtained by manual entry generally. Edit- Reply to Lenze: As for the current coding I'm good for scraping the whole thing and starting over if it would be easier.

    Here, I'll try to reiterate. The Original columns below are merely there to demonstrate the number that is entered into the cell. In Column D and G I'd like to format the cells... The positive to look like Column D and the negative to look like Column G (or negative in parenthesis even it doesn't matter). The kicker is... I want to be able to use the same Custom Number format for both.

    This will come in handy for larger spreadsheets that have mixed + & - data as well as for a charts source data to save time from editing the chart specifics.



    I hope that makes more sense. Would this be possible?

    Thanks to all for reading and any advice or help provided!
    Many thanks,
    Kudos

  6. #6
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Millions & Thousands Custom Number Formatting

    Thanks Lenze! When I try that however it wouldn't format the thousand with a K at the end. The numbers are obtained by manual entry generally.
    Are you sure?
    Code:
    [>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";General
    works for me. That said, however, to get your 4 formats, you will need code.

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  7. #7
    New Member
    Join Date
    Feb 2008
    Posts
    16

    Default Re: Millions & Thousands Custom Number Formatting

    Yeah, if I put in -150000 the formatting doesn't change it, it still looks like -150000. Could it be that it's because I'm using Excel 2007?

    And when you say I'll need code what does that mean exactly? What would that entail?

    Thanks again!
    Kudos

  8. #8
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Millions & Thousands Custom Number Formatting

    Quote Originally Posted by Kudos View Post
    Yeah, if I put in -150000 the formatting doesn't change it, it still looks like -150000. Could it be that it's because I'm using Excel 2007?

    And when you say I'll need code what does that mean exactly? What would that entail?

    Thanks again!
    Kudos
    Right -150000 would show up as entered. The custom format for numbers less than 0 is General.

    By code, I mean a macro, probably a Change Event.
    Let me think about that.

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

  9. #9
    New Member
    Join Date
    Feb 2008
    Posts
    16

    Default Re: Millions & Thousands Custom Number Formatting

    Thanks for all your help Lenze!

  10. #10
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690

    Default Re: Millions & Thousands Custom Number Formatting

    OK. Here goes
    Firstt, create 2 Custom Styles. Format>Styles. I used Style1 and Style2. Name then whatever you want. For Style1 Custom Format using
    Code:
    [>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";General
    For Style2, use
    Code:
    [<-1000000]-$#,##0.0,,"M";General
    Now, in the WorkSheet module, copy this code
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target
        Case Is > -1000000
            Target.Style = "Style1"
        Case Else: Target.Style = "Style2"
    End Select
    End Sub
    You can, if needed, specify the exact Cells, columns or ranges to apply this to. Basically, when a number is entered in a cell, the correct Style(formatting) will be applied.

    Good luck

    lenze
    If you have to tell your boss you're good with Excel, you're NOT!!
    All I know about Excel I owe to my ignorance!
    Scotch: Because you don't solve great Excel problems over white wine

Page 1 of 2 12 LastLast

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
  •  


DMCA.com