Need Help Adjusting Formula for Single Decimal Place and Formatting as Percentage in Smartsheet

System

This discussion was created from comments split from: Add a formatted number to a text string.

Lisa-B

https://community.smartsheet.com/discussion/comment/335265#Comment_335265

That is an awesome formula and want to use it @Berto D —- however it gives me 2 decimal places and I only need 1. I've played with changing all sorts of numbers in the formula (second half) but am not getting it right. Please could you help?

I also need to take a decimal number and make it a formula to show me xx.x% with some text, and although I can do that with the "[cell]@row x 100 + "%", it doesn't always give me the decimal place (1). Any help with this too would be appreciated.

Genevieve P.

Hi @Lisa-B

In the ROUNDDOWN function, you can specify how many decimals to round down to. I've bolded the adjustments below:

="Our project this month is $" + IF(Amount@row > 1000000, INT(Amount@row / 1000000) + "," + RIGHT(INT(Amount@row / 1000), 3) + "," + RIGHT(INT(Amount@row), 3), IF(Amount@row > 1000, INT(Amount@row / 1000) + "," + RIGHT(INT(Amount@row), 3), INT(Amount@row))) + IF(ROUNDDOWN(MOD(Amount@row, 1), 2) < 0.0001, ".0", IF(ROUNDDOWN(MOD(Amount@row, 1), 2) < 0.09999, ".0" + RIGHT(ROUNDDOWN(MOD(Amount@row, 1), 1), 1), IF(LEN(ROUNDDOWN(MOD(Amount@row, 1), 2)) = 3, RIGHT(ROUNDDOWN(MOD(Amount@row, 1), 1), 2) + "0", RIGHT(ROUNDDOWN(MOD(Amount@row, 1), 1), 3)))) + " over budget"

For your second point, you can use ROUNDDOWN there as well:

=ROUNDDOWN([cell]@row * 100, 1) + "%"

Another option would be to simply * (multiply) the value, then use the Formatting options in the top toolbar to both show the percentage and your preferred decimals.

Cheers,
Genevieve