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
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!