Excel custom format for numbers shown as ($K)
I need an Excel custom format to apply K, M, B endings when the original numbers are shown as ($K). For example, $4 is really $4000, and $87.6K is really $87,600,000 or $87.6M. This is common way of improving readability in corporate financial documents where the numbers are all large.
I have an Excel custom format that shows real numbers with their K and M shortened versions:
[>1000000]$#.0,,"M";[>999]
I tried making a second version, but it didn't work. It looks like Excel knows what M and K are and won't let me apply them when the source numbers don't agree with the normal definition.
I have an Excel custom format that shows real numbers with their K and M shortened versions:
[>1000000]$#.0,,"M";[>999]
I tried making a second version, but it didn't work. It looks like Excel knows what M and K are and won't let me apply them when the source numbers don't agree with the normal definition.
If the raw data are text that look like numbers, you must convert them to real numbers before using them in calculations. You can do so with a formula like:
You may then use either your original Custom format, or
[>1000000]$#.0,,\M;[>999]$
=IF(RIGHT(B2,1)="M",1000000,IF(RIGHT(B2,1)="K",1000,1))*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"$",""),"M",""),"K",""),",","")You may then use either your original Custom format, or
[>1000000]$#.0,,\M;[>999]$
If you want to show B for billions, you could use a Custom number format [>1000000000]$#.##0,,,\B;[
and a formula to convert text to numbers like:
and a formula to convert text to numbers like:
=IF(RIGHT(B2,1)="B",1000000000,IF(RIGHT(B2,1)="M",1000000,IF(RIGHT(B2,1)="K",1000,1)))*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"$",""),"B",""),"M",""),"K",""),",","")
I dont get how $87.6K is really $87,600,000 or $87.6M, is it 87.6k or 87.6M ?
ASKER
[Answer to Arana's question] In corporate planning documents, where the numbers are often in thousands, millions, and billions, it's very common for amounts to be shown as $K, meaning that you should add three zeroes onto the end of every number. Fictitious example:
Revenue per region in 2019 ($K):
- EMEA - $190
- APAC - $65
- AMR-N - $478
- LATAM - $32
These tables are often copied from Excel, and the benefit of doing it this way instead of with Ks and Ms is that the numbers remain numbers and can still be added, multiplied, etc. The formula I listed below takes care of that, but it's not common knowledge.
Revenue per region in 2019 ($K):
- EMEA - $190
- APAC - $65
- AMR-N - $478
- LATAM - $32
These tables are often copied from Excel, and the benefit of doing it this way instead of with Ks and Ms is that the numbers remain numbers and can still be added, multiplied, etc. The formula I listed below takes care of that, but it's not common knowledge.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I understand about corporate numbers and the need to keep smaller numbers, its just I was confused about your statement, as I read it i was under the impression that same amount could mean 3 different quantities, which was not the case : if $4 is really $4000 and 86,600,000 was in fact 87.6M why would you want to add 3 zeroes in one case but not in the other, I had to reread it more times to actually understand what you where trying to say.
$#,##0.0,,\M