Nesting SUM, Decimal places, and %

I'm attempting to calculate the percentage of work that's done, round it to a whole number, and add a percent sign. I can get 2 of any 3, but not all three.

=SUM(([Points Done]@row / [Total Points]@row) * 100)

Screen Shot 2020-03-05 at 10.21.22 AM.png

Adding + "%" to the end of the equation gives me 5 decimals.

I'd also like to add an IFFERROR for when there are no points. How do I nest all these together?

Find more posts tagged with

Formulas

functionality

Accepted answers

Mike Wilday

So percentages in Smartsheet are actually calculated as decimals. .1 = 10%, .5 = 50%, .75 = 75%, 1= 100%... if you've set the % row with percentage formatting or are using a predefined row, you should formulate your answer to provide you with a decimal. Did that work? Let us know.

All comments

Mike Wilday

fred sodergren

Let me ask a different way:

=SUM(([Points Done]@row / [Total Points]@row)) results in .63636

=SUM(([Points Done]@row / [Total Points]@row) * 100) results in 63.636363

=SUM(([Points Done]@row / [Total Points]@row) * 100)+ "%" results in 63.636363%

1) How do I specify no decimals, and have a percent sign?

2) If the Points column is empty, how do I incorporate an IFERROR to have a custom message?

3) Anyway to have this in one equation?

My attempts to combine them haven't worked.

Thank you!

Mike Wilday

Have you tried formatting the column as Percentages using the % option in the toolbar?

Use the first formula that results in .63636 and send me a screenshot of the final result!

fred sodergren

I completely missed that button! Thank you, it works perfectly.

This IFERROR works now also, thanks for your help.

=IFERROR(SUM([Points Done]@row / [Total Points]@row), "Unpointed")

Mike Wilday

Awesome. Glad I could help. Please mark my answer as best answer if you think it resolved the situation for you. Have a great day!

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.