How to compute Yes or No variance per line item?

[Pat Abella]

Please help. Ive been looking for the formula for the longest time. How do we compute the variance of a yes or no question per line item?

I attached the sample. Thank you so much.

[Sam Capricci]

could you better define your question AND possibly, based on your attached sample, tell us what the expected output would be?

[Pat Abella]

could you better define your question AND possibly, based on your attached sample, tell us what the expected output would be?

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.

Its a bit harder because the questions are answerable by yes or no and not using points

[davsth]

I do not think you have given us an expected result. Or defined a method particularly,. you do need to give an expected result as requested

Is the calibration over all questions of for each question? Is is whether an individual is within acceptable bounds of whether a proportion of all individuals score a question correctly?
in the example onyl 50% of respondents score the question the same as the bench mark.

[Pat Abella]

I think it will be easier if I do not use yes or no but the actual points for each question?

I've attached the updated file. Sorry I'm really trying to answer your question. Basically, I just need to find out the variance per question.

[samba_ravi]

Will you show your expected results manually

[Pat Abella]

I added the expected results in the attached file. Thank you so much for looking into this.

[davsth]

Your expected results are what the formula should show? or a repeat of the benchmark, which at one level is the expected results
Are your results even from an interval value, as there does not seem to be much of a variety of diferent numbers

[Pat Abella]

Sorry I must have made it more confusing. I'm basically looking to get the variance of each question. Is it possible?

[José Augusto]

Hi

I'm trying

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

[Pat Abella]

Yes I think that's it. Thank you so much!

[davsth]

I am not sure it is
Variance as a statistical term is a measure of how far your data is spread from the mean
The mean is presumably the benchmark, as this is what you are aiming for. In the first solution =VAR.S(C2:G2) the benchmark is not mentioned at all in the calculation

In the second =VAR.S(INDEX(--($C2:$G2=$B2),0)) the values have been converted to binary in 1 equals benchmark 0 it doesn't

if c2:g2 all equal 0 this computes as 0.0, but is 0.3 if 2 equal the benchmark and 2 dont, and 0 if all equal the benchmark. what is this measure telling you? I would suggest nothing, it appears nonsensical. The same result fo reverything right as everything wrong (yes the variance of the sample is 0, but the deviation from the target is total!

i also do not think that c2 should be included in the calculation as it is the expected result!

I am also think you confused as the benchmark is the expected result (its what you want). what you were asked for is what number would you wish for in the shaded column, which you have not provided


Surely you want something as simple (especialy if it is binary reponses) as the % of results that equal the benchmark. 100% everyone got it, 0% noone got it

[Pat Abella]

I am not sure it is
Variance as a statistical term is a measure of how far your data is spread from the mean
The mean is presumably the benchmark, as this is what you are aiming for. In the first solution =VAR.S(C2:G2) the benchmark is not mentioned at all in the calculation

In the second =VAR.S(INDEX(--($C2:$G2=$B2),0)) the values have been converted to binary in 1 equals benchmark 0 it doesn't

if c2:g2 all equal 0 this computes as 0.0, but is 0.3 if 2 equal the benchmark and 2 dont, and 0 if all equal the benchmark. what is this measure telling you? I would suggest nothing, it appears nonsensical. The same result fo reverything right as everything wrong (yes the variance of the sample is 0, but the deviation from the target is total!

i also do not think that c2 should be included in the calculation as it is the expected result!

I am also think you confused as the benchmark is the expected result (its what you want). what you were asked for is what number would you wish for in the shaded column, which you have not provided


Surely you want something as simple (especialy if it is binary reponses) as the % of results that equal the benchmark. 100% everyone got it, 0% noone got it

Hello! I understand your point. I've updated the file and I'd like to know if it will be easier to get the variance this way? In the attached file, QA3 got the same number of points as the benchmark score and may look like he is calibrated, but in reality, he answered question 11 differently. The acceptable variance is +/-5. Will I be able to get that per question and not on the overall score?

[davsth]

=SUMPRODUCT(ABS(($B$2:$B$13-C2:C13))) perhaps for columns


=SUMPRODUCT(ABS(($C2:$F2-B2))) for rows

[Pat Abella]

=SUMPRODUCT(ABS(($B$2:$B$13-C2:C13))) perhaps for columns


=SUMPRODUCT(ABS(($C2:$F2-B2))) for rows

Thank you!

[davsth]

or if it is questions and not differences

=COUNT(C2:F2)-SUMPRODUCT(--($C2:$F2=B2))
or
SUMPRODUCT(--($C2:$F2<>B2))


=SUMPRODUCT(--($B$2:$B$13<>C2:C13))

I should have said that ABS makes things positive so all the differences are positive and so do not cancel each other out when being summed up