Custom Format Cells Formulas

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
Hi,


I need the following to happen

column A contains numbers....
in column B, I am trying to input a formula for custom format: so that the numbers will appear as such

column A: 32,458,000 ; column B will show +32MM
column A: -100,000 ; column B will show -100K

im currently using the below formula, but it doesn't give me the + or - sign

[>=1000000] #,##0.00,,"MM";[>0] #,##0,"K";General


in column
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
format numbers in +/- K's and +/- MM's

I would like to display 110,587 (cell A1) as +111K (cell A2); and -1,678,400 (cell B1) as -1.68MM (cell B2)

does anyone know how to accomplish this?
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Re: format numbers in +/- K's and +/- MM's

And just to be clear, do you want this to be flexible ?
So for example, if you change the value in A1 from 110,587 to, say, 11,058,700, do you want it to then show 11.06MM ?
 

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
Re: format numbers in +/- K's and +/- MM's

Correct, but I would it to show +11.06MM
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Re: format numbers in +/- K's and +/- MM's

Adapting your format from your OP, this will give you the +sign . . .

[>=1000000] +#,##0.00,,"MM";[>0] +#,##0,"K";General

Still working on the negative scenario . . .
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Re: format numbers in +/- K's and +/- MM's

Hi,

It is my believe what you're trying to do is not achievable by Cell Formatting, you can't have all four, +#.##MM and +#.##K and -#.##MM and -#.##K custom formats at the same time.

Have a look here: The Definitive Guide to Custom Number Formats in Excel - Excel Tactics

May be someone can come up with a VBA solution for you.

Or, you can do this with a formula; however, the results will be Text representations of the values. If it's meant for display purposes only, then you may consider the formula option.
 
Last edited:

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
149
Understood. Can you help with the formula. I can also post a new thread looking for vba....
 

Forum statistics

Threads
1,086,246
Messages
5,388,674
Members
402,134
Latest member
McKnze21

Some videos you may like

This Week's Hot Topics

Top