Formula to compute score variance of QA auditors during calibration sessions

[lesterology]

Please help! I am a new QA supervisor and I have 4 QA auditors under me. Attached is how our "variance report" looks like. The goal of the report is to show whether or not we are calibrated in scoring/auditing a call. The acceptable variance is +/-5. Now as you can see, what they do is they get the difference between the "Benchmark Score" and the individual auditors' score. I find it faulty because it does not account for the variance per line item. The example shows that auditor 1 has 0 variance BUT he is, in fact, amiss in two line items (2 and 7), which means, he is not calibrated.
So I've been Googling all day for a template/formula in calculating the variance in this context but I can't find any; or maybe it's too complicated for me to understand. Do you know of a formula I can use? I want to know how to do it, so I can explain it to my colleagues in the office.

variance.JPG

[Pete_UK]

Assume your table starts in A1, then select the cells from C2 to E12 and click on Conditional Formatting | New Rule | Use a formula... , then enter this formula in the dialogue box:

=ABS($B2-C2)>5

Click on the Format button and the Fill tab and choose red. Then click OK twice to exit the dialogue box - Excel will automatically adjust the cell references to suit the cells that had been selected, and you should find a red background for all those values that differ by more than +/- 5 compared with the value in column B.

Hope this helps.

Pete

[Pat Abella]

Hi,

Can anyone help me?

Please help get the variance per line item. I've attached the sample.

[FDibbins]

Hi,

Can anyone help me?

Please help get the variance per line item. I've attached the sample.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread