Millions & Thousands Custom Number Formatting

Kudos

New Member
Joined
Feb 26, 2008
Messages
16
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 :oops:
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.

untitled.bmp


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

Thanks to all for reading and any advice or help provided!
Many thanks,
Kudos
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top