Help on formula: format numbers and shorten to K (thousand), M (million), B (billion)

Grace

Hi all,

So I was able to get help around here on how I could turn amounts in thousands and millions to just K and M instead of displaying lots of zeros. Now I would like to add a formula in to just round it up with just 2 decimal places and to shorten billions too. Can someone help please?

Here's my current formula:

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

Many thanks,

Grace

Robert S.

Hello Grace,

 

Thanks for the question. This formula can be updated to change any amount to an abbreviation in the future by adding another IF statement at the beginning. There's also room from improvement to make the formula smaller. Here's an example of a smaller formula that includes converting billions to B and rounds the result to 2 decimal places:

 

=IF(ABS([Summary Info 2]23) >= 10 ^ 9, ROUND(ABS([Summary Info 2]23) / 10 ^ 9, 2) + "B", IF(ABS([Summary Info 2]23) >= 10 ^ 6, ROUND(ABS([Summary Info 2]23) / 10 ^ 6, 2) + "M", IF(ABS([Summary Info 2]23) >= 10 ^ 3, ROUND(ABS([Summary Info 2]23) / 10 ^ 3, 2) + "K", ABS([Summary Info 2]23))))

 

This reduces the amount of characters needed to write the formula by taking large numbers with repetitive zeros and replacing them with their exponent values, such as instead of 1,000,000,000 I used 10 ^ 9. I also moved the addition of the letter to each of the IF statements that are already looking for how large the number is. For the rounding, I used the ROUND function. More information on this function can be found here if needed (https://help.smartsheet.com/function/round).

 

Hope this helps. Please let me know if you have any questions on this.

Grace

Hi Robert,

You're a lifesaver! Thank you very much for the quick response!

Best,

Grace

Grace

Hi Robert,

Just a follow-up question, I'm getting an error message #CIRCULARREFERENCE for when the value is in thousands. I've tried to change the 3 in 10 ^ 3, 2 to 5 since the value that I am rounding of is in hundred thousands but i'm still getting the same error message. Please can you advise what's the right formula?

Kind regards,

Grace

Robert S.

Hi Grace,

 

You will want to keep the 10 ^ 3 as it is in order to account for values under a hundred thousand. The #CIRCULAR REFERENCE error happens because the formula is referencing itself. More on this error can be found here (https://help.smartsheet.com/articles/2476176#circularreference). This generally happens if the formula is in one of the columns that are being referenced, or it can be more indirectly by having one formula reference a column and then another formula in that column that's referencing the first formula.

 

In this case, the formula is referencing a single cell so it's likely a more indirect cause. This formula is referencing the [Summary Info 2]23 cell, and if I'm not mistaken that cell contains a formula similar to this which was found in a previous question you put in community:

 

=SUMIFS([Negotiated/Potential Savings]:[Negotiated/Potential Savings], [Actual End Date]:[Actual End Date], IFERROR(AND(MONTH(@cell) <= 2, YEAR(@cell) = 2018), false), Status:Status, "Completed")

 

Since this formula is referencing the Negotiated/Potential Savings, Actual End Date, and Status columns, if this new formula is in any of these columns it could be the reason for this error. If this is the case, you should be able to put this new formula into any other column for it to work. If that doesn't seem to be the issue, please provide more information on where this formula is located as well as under what conditions you're seeing the error and I'll be happy to take a look.

Grace

I've got it right now. Thanks a lot Robert!

Cheers,

Grace

Lynell Mulvihill

is there a way to add "$" dollar sign to the ABS if statement? Exp: Currently the formula returns 8.38M but I'd like it to read $8.38M

XuDong W Wang

https://community.smartsheet.com/discussion/comment/263156#Comment_263156

JOIN("$"+cell)

Eliot

Hello. The formula above works nicely. However, can this be modified to ONLY show numbers in Millions? I.e. $1,300,000 would be $1.3M and $700,000 would be $0.7M.

=IF(ABS([Summary Info 2]23) >= 10 ^ 9, ROUND(ABS([Summary Info 2]23) / 10 ^ 9, 2) + "B", IF(ABS([Summary Info 2]23) >= 10 ^ 6, ROUND(ABS([Summary Info 2]23) / 10 ^ 6, 2) + "M", IF(ABS([Summary Info 2]23) >= 10 ^ 3, ROUND(ABS([Summary Info 2]23) / 10 ^ 3, 2) + "K", ABS([Summary Info 2]23))))

Genevieve P.

Hi @Eliot

Sure! You can simply use the middle part of the formula in this case:

=ROUND(ABS([Summary Info 2]@row) / 10 ^ 6, 2) + "M"

Note that I used an @row reference instead of locking the cell to row 23, as in your example above.

Cheers,

Genevieve

Eliot

That was elegent! Thanks! This even worked when changing the formula to reference a SUM of two fields. This would not work on the previous one I had. Thank you.

Eliot

Eliot

However, when presenting these fields in a Report, it does not let me Summarize them (using SUM). It shows 0 and does not total the fields. Any ideas? Count works, but not SUM which is what I would need.

Eliot

Genevieve P.

Hi @Eliot

You won't be able to SUM this data as it has the letter "M" next to it, which turns it into a string of text. You'll need to SUM the original values (the [Summary Info 2] column) and then apply your formula to that result as well.

Eliot

Thank you. This worked. I simply took away the M for this use case, as millions was understood.

cvarela

Hi all,

Thank you for the work you've already done to provide these formulas. I am trying to combine the formula (my version is below) with the dollar sign. I did JOIN in another column with =JOIN("$", [Approved Budget]@row), but all I get is "$". Can someone please help me get the dollar sign to show on the result from:

=IF(ABS([Approved Budget (Whole Number)]@row) >= 10 ^ 9, ROUND(ABS([Approved Budget (Whole Number)]@row) / 10 ^ 9, 2) + "B", IF(ABS([Approved Budget (Whole Number)]@row) >= 10 ^ 6, ROUND(ABS([Approved Budget (Whole Number)]@row) / 10 ^ 6, 2) + "M", IF(ABS([Approved Budget (Whole Number)]@row) >= 10 ^ 3, ROUND(ABS([Approved Budget (Whole Number)]@row) / 10 ^ 3, 2) + "K", ABS([Approved Budget (Whole Number)]@row))))

Genevieve P.

Hi @cvarela

You'll just need to add in the "$" as a string before any of the value if true statements in your formula:

=IF(ABS([Approved Budget (Whole Number)]@row) >= 10 ^ 9, "$" + ROUND(ABS([Approved Budget (Whole Number)]@row) / 10 ^ 9, 2) + "B", IF(ABS([Approved Budget (Whole Number)]@row) >= 10 ^ 6, "$" + ROUND(ABS([Approved Budget (Whole Number)]@row) / 10 ^ 6, 2) + "M", IF(ABS([Approved Budget (Whole Number)]@row) >= 10 ^ 3, "$" + ROUND(ABS([Approved Budget (Whole Number)]@row) / 10 ^ 3, 2) + "K", "$" + ABS([Approved Budget (Whole Number)]@row))))

Cheers,

Genevieve