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

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

Answers

  • Lisa-B
    Lisa-B ✭✭✭
    edited 05/31/24
    Options

    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.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!