0

I use a custom number format for numbers into the billions, which works as expected:

[<1000000]#,##0.0," K";  [<1000000000]#,##0.0,," M";  #,##0.0,,," B"

Now I need to deal with even larger numbers into the trillions, so I tried:

[<1000000000]#,##0.0," M"; [<1000000000000]#,##0.0,," B"; #,##0.0,,," T"

However, this fails - it displays one trillion (1,000,000,000,000,000) as 1,000,000.0 T, which is actually formatted as billions, not trillions.

I've checked for solutions, eg StackOverfow and googled as well, but I can't see why this formatting won't work. NOTE that the suggested SO (linked) format does NOT WORK.

Just trying to avoid slow custom functions etc, so if anyone has a suggestion, it would be most welcome.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
maxhugen
  • 1,870
  • 4
  • 22
  • 44
  • Check your spreadsheet locale settings - depending on the country then 1 trillion is either 10^12 (1,000,000,000,000) or 10^15 (1,000,000,000,000,000) – Rafa Guillermo Dec 01 '21 at 09:15
  • Thanks, but formatting has nothing to do with locale. Instead of M;B;T, they could be formatted as A;B;C or anything else. The issue is that Google Sheets is NOT recognising the format. Try it out yourself - if you can find a way to actually make the formatting work for a trillion, I'd be very interested. Cheers. – maxhugen Dec 02 '21 at 07:32

1 Answers1

2

Documentation excerpt:

The number format documentation says this about the , token:

If it appears between two digit characters (0, # or ?), then it renders the entire number with grouping separators (grouping by the thousands).

So, =TEXT(1000, "0,0") renders 1000 as 1,000(It just adds a , every 3 digits)

If it follows the digit characters, it scales the digits by one thousand per comma (e.g., the format #0.0,, renders the number 12,200,000 as 12.2).

So, =TEXT(1000, "0,") would render 1000 as just 1.

Issue:

[<1,000,000,000]#,##0.0," M";
[<1,000,000,000,000]#,##0.0,," B";
 #,##0.0,,," T"

Only a single , is used for " M"(less than 1 Billion) formatting. So, the number is only scaled by 1000 instead of 1000^2.

Solution:

Add a another ,

Number Fixed Original
Formatting: [<1000000000]#,##0.0,," M"; [<1000000000000]#,##0.0,,," B"; #,##0.0,,,," T [<1000000000]#,##0.0," M"; [<1000000000000]#,##0.0,," B"; #,##0.0,,," T"
10 0.0 M 0.0 M
100 0.0 M 0.1 M
1000 0.0 M 1.0 M
10000 0.0 M 10.0 M
100000 0.1 M 100.0 M
1000000 1.0 M 1,000.0 M
10000000 10.0 M 10,000.0 M
100000000 100.0 M 100,000.0 M
1000000000 1.0 B 1,000.0 B
10000000000 10.0 B 10,000.0 B
100000000000 100.0 B 100,000.0 B
1000000000000 1.0 T 1,000.0 T
10000000000000 10.0 T 10,000.0 T
100000000000000 100.0 T 100,000.0 T
1000000000000000 1,000.0 T 1,000,000.0 T
10000000000000000 10,000.0 T 10,000,000.0 T
TheMaster
  • 45,448
  • 6
  • 62
  • 85