How to remove decimals from a percentage formula with text before it?

JJWBTM

Hi,

I currently have the below formula:

="Completion: " + [Product Data]@row / [Units Sold]@row * 100 + "%"

The cell then shows "Completed: 4.05405%". However, I'd rather this be "4.05%".

Please can someone advise how I can fix this? Fairly new to Smartsheets so I'm still learning!

I've tried to click the 3 dots and remove the decimal spaces but it doesn't work.

Thanks!

Jeff Reisman

I've never used this "ROUND" function before, but it works!

="Completion: " + (ROUND([Product Data]@row / [Units Sold]@row, 4) * 100) + "%"

Syntax is ROUND(number, decimal places)

So in your case, since you want percentage to 2 decimal places, we tell it to give us the dividend rounded to 4 decimal places so that we have 2 decimal places left after multiplying by 100.

Note: Rounds a given number to the desired number of decimal places. Negative number of digits operates to the left of the decimal (-1 rounds to 10s, -2 rounds to 100s, etc.) Link below goes the Smartsheet formula examples sheet row for ROUND.

https://app.smartsheet.com/sheets/mrHG6RhFpp6wQMj4p3h9jgQRWW3Xjhxjr53VJ2v1?rowId=1749615466571652

Jeff Reisman

I've never used this "ROUND" function before, but it works!

="Completion: " + (ROUND([Product Data]@row / [Units Sold]@row, 4) * 100) + "%"

Syntax is ROUND(number, decimal places)

So in your case, since you want percentage to 2 decimal places, we tell it to give us the dividend rounded to 4 decimal places so that we have 2 decimal places left after multiplying by 100.

Note: Rounds a given number to the desired number of decimal places. Negative number of digits operates to the left of the decimal (-1 rounds to 10s, -2 rounds to 100s, etc.) Link below goes the Smartsheet formula examples sheet row for ROUND.

https://app.smartsheet.com/sheets/mrHG6RhFpp6wQMj4p3h9jgQRWW3Xjhxjr53VJ2v1?rowId=1749615466571652

JJWBTM

https://community.smartsheet.com/discussion/comment/318161#Comment_318161

Jeff - you are a lifesaver. I was ripping my hair out for ages trying to figure it out!

Thank you very much.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318175#Comment_318175

Glad I could help. I didn't even know about that function!

Handy links to use when building formulas:

https://help.smartsheet.com/functions

https://help.smartsheet.com/articles/2476176-formula-error-messages

nancy.mina

I am trying to use ROUND on a simple "percent of" formula and getting 0 when I use the suggestion above. Any help is appreciated: (ROUND([<$10M]3 / [# Engaged]3) *100):

9 / 2 = .222222 and * 100 = 22.2222

With ROUND I get 0 and without using ROUND I get 22.22222. I need to show 22 or 22.2 . It looks easy so it's very frustrating that I have not been able to figure this one out. Anyone's help is greatly appreciated!!

nancy.mina

I got it finally! I knew if I asked the answer would come eventually :), I was missing the number within the parenthesis.