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 agree to our terms of service, privacy policy and cookie policy

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