formattingformulasgoogle sheets
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]](https://i.stack.imgur.com/Lwf3o.png)
I looked up a good answer below but I'm not sure how to edit the formula for billions, trillions, and so on.
This option is only available in the "new Google Sheets".
In the new Google Sheets, you can apply custom formatting for currencies, dates, and numbers.
cf https://support.google.com/drive/answer/56470?hl=en
To activate the new Google Sheets, you can follow those instructions: https://support.google.com/drive/answer/3541068
The following custom number format mask will produce the desired results without losing the accuracy of the underlying value.
[>999999]0.0,,\M;[>999]0.0,\K;0
Best Answer
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):