Progress Bar and/or Completion % by Check Box Column Status

Tony Fronza

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 :)

Tony Fronza

Thanks, Genevieve!

I don't know why I used IFS above (I originally used AND). Thanks for clarifying, though!

Paul Newcome

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.

Tony Fronza

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.

Tony Fronza

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?

Genevieve P.

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

Tony Fronza

Thanks, Genevieve!

I don't know why I used IFS above (I originally used AND). Thanks for clarifying, though!

Tony Fronza

@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?

Genevieve P.

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  

Tony Fronza

Thanks, @Genevieve P. ! That worked perfectly!