How to remove decimals from a percentage formula with text before it?
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!
Best Answer

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
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

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
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

Glad I could help. I didn't even know about that function!
Handy links to use when building formulas:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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!!

I got it finally! I knew if I asked the answer would come eventually :), I was missing the number within the parenthesis.
Help Article Resources
Categories
Check out the Formula Handbook template!