Forum Discussion

smodkins's avatar
smodkins
Copper Contributor
Oct 16, 2022

Percentage Complete for text fields with variable N/A value

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 

developer
Lists
microsoft lists
SharePoint Online
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Oct 17, 2022

    smodkins 

     

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

  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Oct 17, 2022

    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)

Resources