Percentage Complete for text fields with variable N/A value

Occasional Visitor

Hello!  I am hoping someone can help with the following:

 

I have 9 columns in a sharepoint, each one representing a type of check. I want a column after that calculates the percentage of checks that are clear. Not all checks are applicable to check record so if their value is "N/A" I don't want them to be included in the calculation.

For the purposes of the formula you can call them "Col1" to "Col9"

 

The three possible choices in the column are:

  • Pending
  • Passed
  • N/A

 

So if all 9 checks are relevant they will begin as Pending. If 6 change to passed then the formula would return a value of 67% clear.

 

If any of the columns are set to N/A then they should not be included. So if 1 column is set to N/A, 4 are set to pending, 4 are set to Passed then the formula should return 50%

 

Thanks in advance 

2 Replies

Edited after re-reading your post

 

@smodkins in my example the columns are Check1 to Check 9.

 

CheckItems is a calculated column with the formula

 

=IF(Check1="N/A",0,1)
+IF(Check2="N/A",0,1)
+IF(Check3="N/A",0,1)
+IF(Check4="N/A",0,1)
+IF(Check5="N/A",0,1)
+IF(Check6="N/A",0,1)
+IF(Check7="N/A",0,1)
+IF(Check8="N/A",0,1)
+IF(Check9="N/A",0,1)

 

The CheckClear is a calculated column set to 0 decimal places and the ClearPC column is a calculated column set to 0 decimal points and as a percentage. The formula for the CheckClear is

 

=IF(Check1="Passed",1,0)
+IF(Check2="Passed",1,0)
+IF(Check3="Passed",1,0)
+IF(Check4="Passed",1,0)
+IF(Check5="Passed",1,0)
+IF(Check6="Passed",1,0)
+IF(Check7="Passed",1,0)
+IF(Check8="Passed",1,0)
+IF(Check9="Passed",1,0)

 

The formula for the ClearPC column is =CheckClear/CheckItems

 

See the post below for an image of the result.

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

@smodkins 

check.png

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)