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!

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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.

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.