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.
Best Answer
-
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.
Answers
-
What is the formula you are using to populate the percentage complete column?
-
@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")
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!