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

Any recipes out there for a "smart" number formatting formula for Google Sheets that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method?

I would expect to enter a number like: 1,816,724 and see 1.8M (or enter 2,394 and see 2.4K).

• It appears that you'd like to enter a number into a cell and see it formatted differently in the same cell. This is not something that formulas can do; a formula can't be based on the value of a cell, and would be overwritten by that value anyway. A script could replace your number with a formatted string, but they you won't have the original number there, and will not be able to use the value in calculations. So, I think the best you can do is to format the cells to use scientific notation: 1.8E+6 instead of 1.8M. – user79865 May 12 '15 at 18:06

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
``````

• How do you add the K notation for negatives as well? – tsujp Dec 10 '17 at 5:36
• You can add up to 3 conditions and have one default. – Jeeped Dec 10 '17 at 6:22
• Ah, so you cannot have M, K, as well as negative M and negative K in the same one formatting rule. – tsujp Dec 10 '17 at 6:47
• Any workarround for the negative numbers? – Emilio Nicolás Mar 5 '18 at 16:48
• `[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"` this custom formatting is almost perfect. How can numbers up to 999 can be excluded to be represented as is? – Elad Ratson Mar 13 at 15:53

found it!

Same formula but with K, M and B!!! And also for negatives.

Article on the matter here: https://medium.com/@brookinc/the-only-spreadsheet-custom-number-format-you-ll-ever-need-7cc356a7ca3e

GL HF

• This is almost a link only answer. Please add the most relevant parts of the referred link or describe how it help you to do what the OP is looking for. – Rubén Feb 11 at 3:58