Is it possible to format numbers in K (thousands) or M (millions)?

Grace

Hi all,

Is there a way to format numbers is thousands or millions to just K or M? Or any formula to do it?

Kind regards,

Grace

ricki

I dont see a format to do it but i think you could do it with a formula like the following

 

=IF(ABS([Column4]2) >= 1000000, (ABS([Column4]2) / 1000000), IF(ABS([Column4]2) >= 1000, ABS([Column4]2) / 1000, ABS([Column4]2))) + IF(ABS([Column4]2) >= 1000000, "M", IF(ABS([Column4]2) >= 1000, "K", ""))

 

 

Grace

Hi Ricki,

Thanks for sharing the formula. It did work!

Many thanks,

Grace

Grace

Hi Ricki,

Follow-up question regarding this topic, please can you advise how can to display just 2 decimal places and what to add in the formula for amounts in billion?

Many thanks,

Grace

ricki

Try taking the abs out of the formula?

Grace

Hi Ricki,

Unfortunately it didn't work. The results says #UNPARSEABLE.

=IF([Summary Info 2]23) >= 1000000, ([Summary Info 2]23) / 1000000), IF([Summary Info 2]23) >= 1000, ([Summary Info 2]23) / 1000, ([Summary Info 2]23))) + IF([Summary Info 2]23) >= 1000000, "M", IF([Summary Info 2]23) >= 1000, "K", ""

ricki

I think thats just a parenthesis issue. try the following

 

=IF([Summary Info 2]23 >= 1000000, ([Summary Info 2]23 / 1000000), IF([Summary Info 2]23 >= 1000, [Summary Info 2]23 / 1000, [Summary Info 2]23)) + IF([Summary Info 2]23 >= 1000000, "M", IF([Summary Info 2]23 >= 1000, "K", ""))

RicardoSJ

I needed this too and Smartsheet was giving way to many figures past the decimal, so this formula should work (Column named "Price"):

="£" + (IF(ABS([Price]@row) >= 1000000, (ROUND([Price]@row / 1000000,0) + "M"),(IF(ABS([Price]@row) >= 1000, (ROUND([Price]@row / 1000,0) + "K")