Million Dollar Format $#,##M and $100,000 as $100K

Hi

Looking for the format code to have this format: $#,##M

I only want to see 2 digits past the comma and round up or down


For example - Want to see: $1.25M - Not $1,247M

Also, want to show $100,000 as $100K within same cell if sum total is below $1M

Thanks

XL&ME

Entered your suggested format & not working

What does your source data look like. Maybe upload some sample data in a workbook. Read the yellow banner at the top of this page on how to do that.

Just using a simple sum function

=SUM(Q12:Q20)

Estimating a construction project that is likely going to be over $1M

Want to see: $1.25M - Not $1,247M

josephteh

This works!!

I had something similar but appears I had a comma placement issue causing error

Thanks

josephteh and others

The, $#,###.00,,"M" format works great to show $1.25M

Now, if I need to show sum total less than $1 Million - How to get it to show $800K within the same cell?


Thanks

Please try

[>999999]$0.00,,\M;[>999]$0,K;$0

Bo_Ry

Sweet.... [>999999]$0.00,,\M;[>999]$0,K;$0

Looks a bit odd but it works nicely!!!

What does the " [ " do?

It is a custom number format based on conditions.

Check here
https://www.ablebits.com/office-addi...number-format/