Average of Pass/Fails, not percentages

Katherine Camacho

Hi, I have a sheet that gives a "score" of Pass or Fail. Multiple rows per user. I originally had percentages as the score and we created a metric sheet that gathered the scores and gave an average per user per month. Since we changed it to pass/fail per score the metric now reads #DIVIDE BY ZERO ...is there a way to still average the amount of pass versus fail on the metric? below is the formula we had.

=AVG(COLLECT({QA - IBT Audit Review - 8 Category -Score}, {QA - IBT Audit Review - 8 Category - Month}, "January", {QA - IBT Audit Review - 8 Category -Employee Email}, [Employee Email]@row))

Jeff Reisman

Perhaps a helper column would do the trick. In your main sheet, add a column called "PassFail" with the formula which checks your Score column, and assigns a 1 for Pass, and a 0 for Fail.

=IF(Score@row = "Pass", 1, 0)

Back on your metrics sheet, alter your formula to collect the AVG of the values from PassFail column instead of from the Score column.

=AVG(COLLECT({QA - IBT Audit Review - 8 Category -PassFail}, {QA - IBT Audit Review - 8 Category - Month}, "January", {QA - IBT Audit Review - 8 Category -Employee Email}, [Employee Email]@row))

Format the column for %, and you'll have your percentage of "Pass" for each employee.

Jeff Reisman

Perhaps a helper column would do the trick. In your main sheet, add a column called "PassFail" with the formula which checks your Score column, and assigns a 1 for Pass, and a 0 for Fail.

=IF(Score@row = "Pass", 1, 0)

Back on your metrics sheet, alter your formula to collect the AVG of the values from PassFail column instead of from the Score column.

=AVG(COLLECT({QA - IBT Audit Review - 8 Category -PassFail}, {QA - IBT Audit Review - 8 Category - Month}, "January", {QA - IBT Audit Review - 8 Category -Employee Email}, [Employee Email]@row))

Format the column for %, and you'll have your percentage of "Pass" for each employee.

Katherine Camacho

@Jeff Reisman THANK YOU! That was perfection in an answer and worked beautifully!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/317677#Comment_317677

Happy I could help!