formula911
New Member
- Joined
- Nov 29, 2020
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
I'm using Excel to calculate quality assessment scores conducted by our quality auditors as they audit our order team's order processing quality.
Our auditors are filling out a form with questions about each quality element that captures a Yes, Partial, and No based on how successfully the employee completed each aspect of our order processing flow based on their department's quality criteria. That form data is being captured in an Excel worksheet where I'm converting each question's text response into weighted scores using a separate weighted scoring table, then capturing a total score out of 100 for each audit entry.
That's been working great but now the auditors would like some questions to be answered as "NA" if the quality criteria is not applicable to the specific audit they're entering. In those cases, the audit's score can't be calculated as simply as adding up the total values captured for each audit entry - I now need to exclude any NA values and do the calculation based on only the questions that were submitted with quality scoring results, so instead of out of 100 that denominator needs to vary when a question is submitted with an NA value.
I've made a mockup table below. Range A2:G5 contains each audit's scoring by order and employee, which I'm calculating using the form response results for each question by matching them up the associated value in the weighted scoring table (range A8:E13).
Column H what I have today - just a simple total of the audit's scores. Since it's out of a 100 possible points if the audit responses were "Yes" for all questions, the total is the same as a quality score percentage without me having to do any further calculations.
Column I is where I've been trying to figure out how to calculate the maximum number of possible points so I can factor out any questions that contain the NA value "X". Once I can calculate max points based on response values, I figure I can use column J to do a simple quality score percentage calculation by dividing the total scored points by the max points.
My initial thinking was that for each audit, I'd be able to look at the values in the audited orders range C:G to then add the up the values in the weighted scoring table's values in column B (the "Yes" results being the max value for each question) and sum up only the Yes values for questions that do not have the NA value of "X" in the audit results. But I haven't found a way to do any kind of conditional summing when the conditions I'm using are not all in the same table. I've tried various combinations of SUMIF, index/matches, and array formulas to achieve the result I'm looking for with no success.
TLDR;
I want to be able to calculate the maximum quality score each audit can have by looking at the scoring results for Q1-Q5 for the audit and adding up the maximum available for each result where there is a non-NA value to get the maximum score excluding NA answered questions, then take the total score divided by this varying maximum and produce a quality score percentage.
Our auditors are filling out a form with questions about each quality element that captures a Yes, Partial, and No based on how successfully the employee completed each aspect of our order processing flow based on their department's quality criteria. That form data is being captured in an Excel worksheet where I'm converting each question's text response into weighted scores using a separate weighted scoring table, then capturing a total score out of 100 for each audit entry.
That's been working great but now the auditors would like some questions to be answered as "NA" if the quality criteria is not applicable to the specific audit they're entering. In those cases, the audit's score can't be calculated as simply as adding up the total values captured for each audit entry - I now need to exclude any NA values and do the calculation based on only the questions that were submitted with quality scoring results, so instead of out of 100 that denominator needs to vary when a question is submitted with an NA value.
I've made a mockup table below. Range A2:G5 contains each audit's scoring by order and employee, which I'm calculating using the form response results for each question by matching them up the associated value in the weighted scoring table (range A8:E13).
Column H what I have today - just a simple total of the audit's scores. Since it's out of a 100 possible points if the audit responses were "Yes" for all questions, the total is the same as a quality score percentage without me having to do any further calculations.
Column I is where I've been trying to figure out how to calculate the maximum number of possible points so I can factor out any questions that contain the NA value "X". Once I can calculate max points based on response values, I figure I can use column J to do a simple quality score percentage calculation by dividing the total scored points by the max points.
My initial thinking was that for each audit, I'd be able to look at the values in the audited orders range C:G to then add the up the values in the weighted scoring table's values in column B (the "Yes" results being the max value for each question) and sum up only the Yes values for questions that do not have the NA value of "X" in the audit results. But I haven't found a way to do any kind of conditional summing when the conditions I'm using are not all in the same table. I've tried various combinations of SUMIF, index/matches, and array formulas to achieve the result I'm looking for with no success.
TLDR;
I want to be able to calculate the maximum quality score each audit can have by looking at the scoring results for Q1-Q5 for the audit and adding up the maximum available for each result where there is a non-NA value to get the maximum score excluding NA answered questions, then take the total score divided by this varying maximum and produce a quality score percentage.
