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

Find more posts tagged with

Formulas

Comments

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

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")

Sign in to join the conversation!

Quick Links

Trending discussions

Form with multi drop down answers - need help to count correct choices
Hi All, I have built a post training test form for a colleague, some of the questions have got multi answer choices (e.g. correct answers can be A, B, C, D or even choice of E - stating that all previous choices are correct; or could be that A,C,D are correct). If correct choice is chosen, needs to return point 1 per each…
Formula for impact score x likelihood
Hi i currently have the below formula but need help amending this =IF([Impact Score]@row = "", "", [Impact Score]@row * [Likelihood Score]@row I need to amend this for if column type is risk then impact score* likelihood score and if column type is issue then return impact score only Thanks
JOIN COLLECT formula for multiple successors
This discussion was created from comments split from: Return a task name that corresponds to the row in the predecessor column.