Formula for % complete.

Laura Bell

Hi. I have a sheet made up with the column below. I am trying to make the percentage complete auto-fill based on which boxes are checked. I am not sure how I can efficiently do this. I can't figure out how to "add" the check boxes. I am hoping there is a way that I could have 9 options of % Complete.

Pulled=50%

Pulled+Source Termed=70%

Pulled+Source Termed+Source QA/QC=75%

Pulled +Source Termed+Source QA/QC+Device/instrument termed=95%

Pulled + Source Termed+Source QA/QC+Device/instrument termed+Device QA/QC=100%

Pulled+Source Termed+Device/instrument termed=90%

Pulled+Source Termed+Device/instrument termed+Device QA/QC=95%

Pulled+Device/instrument termed=70%

Pulled+Device/instrument termed+Device QA/QC=75%

David Joyeuse

Hi @Laura Bell

I would use helper columns for this.

Add hidden columns with those formulas:

• Pulled Value: =IF([Pulled]@row=1,0.5,0)
• Source Termed Value: =IF([Source Termed]@row=1,0.2,0)
• Source QA/QC Value: =IF([Source QA/QC]@row=1,0.05,0)
• Device/instrument termed Value: =IF([Device/instrument termed]@row=1,0.2,0)
• Device QA/QC Value: =IF([Device QA/QC]@row=1,0.05,0)

Then in your final column: [% Complete], just add the Value columns.

=SUM([Pulled Value]@row, [Source Termed Value]@row, [Source QA/QC Value]@row, [Device/instrument termed Value]@row, [Device QA/QC Value]@row)

It'll be easier in the future if you want to change values or add new stages without reworking the whole formula. And also covers every possibilities, including the 9 you highlighted.

Hope it helped!

Jason Tarpinian

It looks like you just need a lot of IF + per column. This formula just looks at each individual column, and if it is checked ( =1), then assign it a % complete, otherwise 0. From your example, it doesn't look like any of the check boxes change the value of % complete when paired with others, so I've assigned the column % complete above each checkbox to confirm. If they are different, just adjust the decimals in the formula below.

=IF(Pulled@row = 1, 0.5, 0) + IF([Source Termed]@row = 1, 0.2, 0) + IF([Source QA/QC]@row = 1, 0.05, 0) + IF([Device/Instrument Termed]@row = 1, 0.2, 0) + IF([Device QA/QC]@row = 1, 0.05, 0)

John Jonassen

Do each column/step in this process equal a percentage complete of it's own? Meaning, "Pulled" is obviously 50%. Does this mean that "Source Termed" is 20%? If so, add a helper row to the top (row 1) that shows each step's individual %. Then in your % Complete column, do a SumIf of the different steps that will add up those percentages if the different boxes are checked within the row.

=sumif(([PULLED]2:[DEVICE QA/QC]2,1,[PULLED]$1:[DEVICE QA/QC]$1)

David Joyeuse

Hi @Laura Bell

I would use helper columns for this.

Add hidden columns with those formulas:

• Pulled Value: =IF([Pulled]@row=1,0.5,0)
• Source Termed Value: =IF([Source Termed]@row=1,0.2,0)
• Source QA/QC Value: =IF([Source QA/QC]@row=1,0.05,0)
• Device/instrument termed Value: =IF([Device/instrument termed]@row=1,0.2,0)
• Device QA/QC Value: =IF([Device QA/QC]@row=1,0.05,0)

Then in your final column: [% Complete], just add the Value columns.

=SUM([Pulled Value]@row, [Source Termed Value]@row, [Source QA/QC Value]@row, [Device/instrument termed Value]@row, [Device QA/QC Value]@row)

It'll be easier in the future if you want to change values or add new stages without reworking the whole formula. And also covers every possibilities, including the 9 you highlighted.

Hope it helped!

Laura Bell

Thank you all so much! Everyone is very helpful. I am going to try the hidden or "helper" columns and see if I can get that to work. I really appreciate the help!