3

Does anyone know a generalized Custom Number Format I can apply in Google Sheets such that numbers appear with it's scale operator and rounded to one decimal place?

Example:

Numbers with Scale Operator[1]

I looked up a good answer below but I'm not sure how to edit the formula for billions, trillions, and so on.

Formula for smart, scale-aware number-formatting in Google Sheets? (e.g. 1,024 → 1k, 1,816,724 → 1.8M)

2

Unfortunately the partial solution found does not appear to be extendable beyond two numeric ranges, so as an alternative there is an extendable but not generalised solution that converts integers greater than 999 to text in order to display as required up to trillions (short scale) but not quadrillion or above (though more terms may be added):

=if(A1<1000,A1,if(A1<1000000,text(A1/1000,"#.0")&"K",if(A1<1000000000,text(A1/1000000,"#.0")&"M",if(A1<1000000000000,text(A1/1000000000,"#.0")&"B",text(A1/1000000000000,"#.0")&"T"))))

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.