Progress Bar and/or Completion % by Check Box Column Status
Hello! I'm looking to use a formula that will calculate the percentage of tasks completed by row based on whether or not the check box is "checked." I have five check box columns, so if one is checked, the % Completed column shows 20%, if four are checked, 80%, etc. I know I need to use IF/And statements, but I keep getting errors.
Here's what I have done thus far to capture 100% for all columns when checked:
=IF(AND([1st Meeting Complete?]@row = 1, [SME Follow-up Meeting?]@row = 1, [Quote/Proposal Issued?]@row = 1, [Finalist?]@row = 1, Resolution@row = 1, "1"))))
Any help or guidance is greatly appreciated :)
Best Answer
-
Thanks, Genevieve!
I don't know why I used IFS above (I originally used AND). Thanks for clarifying, though!
Answers
-
You need to close out the AND after the last criteria - Resolution@row = 1 - and you would only have a single closing parenthesis on the end of that formula since you only have 1 IF statement.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks, Paul!
So, should I add a parenthesis after Resolution@row = 1?
=IFS([1st Meeting Complete?]@row = 1, [SME Follow-up Meeting?]@row = 1, [Quote/Proposal Issued?]@row = 1), 1)
I'm still getting an UNPARSEABLE error, so I'm definitely doing something wrong.
-
Disregard! I just remembered that I need to correct another error. It is working now.
So, should I continue the IF/AND logic for the other scenarios as follows:
=IFS([1st Meeting Complete?]@row = 1, [SME Follow-up Meeting?]@row = 0, [Quote/Proposal Issued?]@row = 0), .33)
Also, one of the five columns is a Yes/No dropdown - how do I include "If (Column@row) is NOT BLANK?
-
Hi @Tony Fronza
To clarify, you'll always want to use IF(AND(...) versus IFS .
Here's how the structure would go with multiple statements:
=IF(AND(---), 1, IF(AND(---), 0.8, IF(AND(---), 0.6, IF(AND(---), 0.4, IF(AND(---), 0.2, 0)))))
My personal favourite way to say "not blank" is to use <> for does not equal and "" for blank, like so:
[Column Name]@row <> ""
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thanks, Genevieve!
I don't know why I used IFS above (I originally used AND). Thanks for clarifying, though!
-
@Genevieve P. Sorry, one last question. I'm trying to get the Completion % to populate as 100%, but it keeps showing up as "1" instead. I have the column formatted as % - do I need to modify the formula to accommodate?
-
Hi @Tony Fronza
Make sure that the output of your formula is a number, as in it's not in quotes.
For example:
=IF(AND(---), 1,
but not
=IF(AND(---), "1",
Putting quotes around a number means that the cell will read it as text and will be unable to add a numerical percent format to it.
Let me know if that helps!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thanks, @Genevieve P. ! That worked perfectly!
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!