1

I have a questionnaire that populates a SharePoint list. There are a number of questions that return answers as picked from a choice column.

I have a calculated column that I'm trying to get to return a value as a result. There are 4 columns that can return a string value, the formula so far will interpret these values as a numerical value. The below formula works when none of the columns are blank.

Currently it returns a sum, but the objective is to return a percentage which ignores unanswered questions. So if Col1 is blank, it is assumed the question did not apply and should not affect the %, which means I can't use if([Col1]="High",3, if([Col1]="Med",2, if([Col1]="Low",1,0))) as a 0 value would negatively affect the percentage. I've tried a few variations of the below formula by adding in ISBLANK but these keep returning #Value! and #Name? for me.

Current Formula:

=SUM(
       if([Q1]="High",3, if(Q1]="Med",2, if([Q1]="Low",1)))
     , if([Q2]="High",3, if([Q2]="Med",2, if([Q2]="Low",1)))
     , if([Q3]="Often",3, if([Q3]="Monthly",2, if([Q3]="Never",1)))
     , if([Q4]="Yes",3, if([Q4]="Planned",2, if([Q4]="No",1)))
    )

What I would like to be able to do is divide the sum of these values by the number of questions that were not blank

Attempted formula (partial):

=SUM(IF(ISBLANK(Q1),"",1),IF(ISBLANK([Q2]),"",1))

The above returns 2 when Q1 and Q2 have answers, but when blank, it returns #Value? I assume because it is trying to add a number to a string.

2
  • Try using COUNT function which counts the number of arguments that contain numbers. Let me know it solves your problem. Aug 24, 2022 at 16:34
  • 1
    Thanks @GaneshSanap I was able to form a solution using CountA
    – MH5
    Aug 25, 2022 at 15:47

2 Answers 2

0

Try using COUNT function which counts the number of arguments that contain numbers.

For example:

=COUNT(IF(ISBLANK([Q1]),"",1),IF(ISBLANK([Q2]),"",1))

For other available functions, refer documentation: Examples of common formulas in lists

0

Thanks @Ganesh Sanap

I was able to achieve the intended result using count as per below:

=(SUM(
       if(
            [Q1]="High",3, 
            if(
                [Q1]="Med",2,
                if(
                    [Q1]="Low",1,0
                    )
                )
            )
     , 
        if(
            [Q2]="High",3,
            if(
                [Q2]="Med",2,
                if(
                    [Q2]="Low",1,0
                    )
                )
            )
     , 
        if(
            [Q3]="Often",3,
            if(
                [Q3]="Monthly",2,
                if(
                    [Q3]="Never",1,0
                    )
                )
            )
     ,
        if(
            [Q4]="Yes",3,
            if(
                [Q4]="Planned",2,
                if(
                    [Q4]="No",1,0
                    )
                )
            )
    )
    -
    CountA(
        [Q1],
        [Q2],
        [Q3],
        [Q4]
    )
)
/
(
    CountA(
        [Q1],
        [Q2],
        [Q3],
        [Q4]
    )
*2
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.