In Smartsheet why will the overall % not populate when the column is highlighted?

In Smartsheet for my percentage column it is only showing me a count when highlighted not the % avg. When I am trying to pull this data to other reports the formulas come back # Divide By Zero but the data is there. Please see screenshots below first image is the original data that does not show the overall avg when highlighted.

On the next sheet that I am trying to pull the data to here is the formula

=AVG(COLLECT({Percentage Complete %}, {Cohort Status BP}, "Complete", {Region BP}, "Midwest"))

Here is the message I receive.

Any help would be appreciated.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Ok. So the first formula is the issue. Wrapping a number in quotes outputs as a text value that only looks like a number.

Percentages are also calculated a little differently. They are calculated as a part of 1. so 100% = 1, 50% = 0.50, 25% = 0.25, so on and so forth.

Try adjusting each output accordingly so that it outputs a decimal (or 1 for 100%) then adjust the column formatting to be shown as a percentage.

All comments

Paul Newcome

What is the formula you are using to populate the percentage complete column?

Ashlie S

@Paul Newcome Here is the column formula

=IF(AND(Role@row = "HCP", [Number Attended]@row = 7), "100%", IF(AND(Role@row = "HCP", [Number Attended]@row = 6), "87.7%", IF(AND(Role@row = "HCP", [Number Attended]@row = 5), "71.5%", IF(AND(Role@row = "HCP", [Number Attended]@row = 4), "57.2%", IF(AND(Role@row = "HCP", [Number Attended]@row = 3), "42.9%", IF(AND(Role@row = "HCP", [Number Attended]@row = 2), "28.6%", IF(AND(Role@row = "HCP", [Number Attended]@row = 1), "14.3%", IF(AND(Role@row = "HCP", [Number Attended]@row = 0), "0%", IF(AND(Role@row = "PCC", [Number Attended]@row = "5"), "100%", IF(AND(Role@row = "PCC", [Number Attended]@row = "4"), "80%", IF(AND(Role@row = "PCC", [Number Attended]@row = "3"), "60%", IF(AND(Role@row = "PCC", [Number Attended]@row = "2"), "20%", IF(AND(Role@row = "PCC", [Number Attended]@row = "1"), "20%", IF(AND(Role@row = "PCC", [Number Attended]@row = "0"), "0%", IF(AND(Role@row = "Extern", [Number Attended]@row = "6"), "100%", IF(AND(Role@row = "Extern", [Number Attended]@row = "5"), "83.4%", IF(AND(Role@row = "Extern", [Number Attended]@row = "4"), "66.7%", IF(AND(Role@row = "Extern", [Number Attended]@row = "3"), "50%", IF(AND(Role@row = "Extern", [Number Attended]@row = "2"), "33.3%", IF(AND(Role@row = "Extern", [Number Attended]@row = "1"), "16.7%", IF(AND(Role@row = "Extern", [Number Attended]@row = "0"), "0%", IF(AND(Role@row = "Apprentice", [Number Attended]@row = 7), "100%", IF(AND(Role@row = "Apprentice", [Number Attended]@row = 6), "87.7%", IF(AND(Role@row = "Apprentice", [Number Attended]@row = 5), "71.5%", IF(AND(Role@row = "Apprentice", [Number Attended]@row = 4), "57.2%", IF(AND(Role@row = "Apprentice", [Number Attended]@row = 3), "42.9%", IF(AND(Role@row = "Apprentice", [Number Attended]@row = 2), "28.6%", IF(AND(Role@row = "Apprentice", [Number Attended]@row = 1), "14.3%", IF(AND(Role@row = "Apprentice", [Number Attended]@row = 0), "0%")))))))))))))))))))))))))))))

I also have a helper column in front of that and here is that formula

=COUNTIF([Clinical Connect Webinar]@row, "Attended") + COUNTIF([Clinical Connect Webinar]@row, "Excused") + COUNTIF([RCM Credentialing Webinar]@row, "Attended") + COUNTIF([RCM Credentialing Webinar]@row, "Excused") + COUNTIF([HR Webinar]@row, "Attended") + COUNTIF([HR Webinar]@row, "Excused") + COUNTIF([HR Webinar]@row, "N/A") + COUNTIF([ATRT Overview Day 1 Webinar]@row, "Attended") + COUNTIF([ATRT Overview Day 1 Webinar]@row, "Excused") + COUNTIF([ATRT Overview Day 2 Webinar]@row, "Attended") + COUNTIF([ATRT Overview Day 2 Webinar]@row, "Excused") + COUNTIF([Compliance Webinar]@row, "Attended") + COUNTIF([Compliance Webinar]@row, "Excused") + COUNTIF([Performance Development Webinar]@row, "Attended") + COUNTIF([Performance Development Webinar]@row, "Excused")

Paul Newcome

Ok. So the first formula is the issue. Wrapping a number in quotes outputs as a text value that only looks like a number.

Percentages are also calculated a little differently. They are calculated as a part of 1. so 100% = 1, 50% = 0.50, 25% = 0.25, so on and so forth.

Try adjusting each output accordingly so that it outputs a decimal (or 1 for 100%) then adjust the column formatting to be shown as a percentage.

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.