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.