Custom Format Cells Formulas

[banneduser123]

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

 

[banneduser123]

Any help would be appreciated

 

[banneduser123]

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]

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 ?

 

[banneduser123]

Re: format numbers in +/- K's and +/- MM's

Correct, but I would it to show +11.06MM

 

[banneduser123]

Re: format numbers in +/- K's and +/- MM's

Can I get any help?

 

[Macropod]

Re: format numbers in +/- K's and +/- MM's

rishijain11: Kindly don't post questions about the same topic in multiple threads.
Please read our policy on Duplicate-Posting in rule #12: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

I have now merged your two threads.

 

[Gerald Higgins]

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]

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.

 

[banneduser123]

Understood. Can you help with the formula. I can also post a new thread looking for vba....