This query is evaluating the responses in the column and applying a value based on the response. Then it divides the sum of the values by the count of how many responses were provided giving us a score for the column.The problem I am running into is, there may not be a response for each employee in every column, so I am getting #NUM! errors. I have tried the following formula and various other versions of the same but either I still get the #NUM! error or I get an error telling me my query doesn’t include the specified expression.
Code:
Sum(IIf([AutoCoverage]="","",IIf([AutoCoverage]="Yes",1,IIf([AutoCoverage]="Partially",0.5,IIf([AutoCoverage]="N/A",1,0)))))/IIf([AutoCoverage]="","",Count([AutoCoverage]))
Code:
Sum(IIf([AutoCoverage]="Yes",1,IIf([AutoCoverage]="Partially",0.5,IIf([AutoCoverage]="N/A",1,0))))/IIf([AutoCoverage]="",0,Count([AutoCoverage])
Code:
Sum(IIf([AutoCoverage]="Yes",1,IIf([AutoCoverage]="Partially",0.5,IIf([AutoCoverage]="N/A",1,0))))/IIf([AutoCoverage]=0,0,Count([AutoCoverage])
I basically just want the cell left blank if the field does not have a response.Thank you for any help you can provide on this!