Percentage Complete for text fields with variable N/A value

[smodkins]

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]

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)

[#3]

@smodkins 

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