Percentage formula based on a checkbox.
Any suggestions on how to make this exceptions formula more accurate? Smartsheets keeps adding 10 rows to my sheet with no data causing my formula to be off.
I have 88 rows with data and a total of 98 rows. Out of the 88 row 21 are an exception with the checkbox selected.
This is the formula I am using.
=COUNTIF({Master Sheet Exceptions}, 1) / COUNT({Master Sheet Exceptions})
The answer should be 23% (21*100)/88=23.86 but Smartsheet is giving me a 21% (21/100)/98=21.43.
Answers
-
That's annoying. Is there another column you could reference that's always populated, so you can change the "COUNTIF" to a "COUNTIFS" and use that other column as another criteria?
I.e. = COUNTIFS({Master Sheet Exceptions}, {Master Sheet Exceptions}, 1, {Non-blank reference column}, <>"")/ COUNTIFS({Master Sheet Exceptions},{Non-blank reference column}, <>"")
If you have a Row ID column, I don't think the row ID would populate unless you added other data to the row--you could potentially use that as your non-blank reference.
-
@Lucas Rayala That is a great idea. I will tinker with the other rows to see what other criteria I can build into it.
-
@AlexisJ glad to help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!