Standared Deviation Formula...what is it actually giving me?

[bgromis]

Hey all,

I am new to the forums, but a long time site user and I was hopeing someone out there would be able to help me figure this out.

I am working on a project in which we are attempting to compare some recorded numbers.

What we have:
A = the number of issues assigned to a specific person
B = the percentage of the number of issues assigned to the person that were completed on time
C = the sum of all issues assigned to all the users

What we are looking to do:
We want to use the A and B values from above to establish a ranking system that will allow us to determine who is doing the best overall. We need to take into account the number of issues that were assigned to make sure that someone that attends to 100 issues and is on time 100% gets more credit than someone who attends to 10 issues and is on time 100%. It does not matter how they are ranked or with what values, as long as it is a consistent result.

The formula I have in Excel right now:
=STDEV(A,B,C)

What it is giving to me:
It is giving me a number that appears to be correct, but it is inverted. So the lower the number, the better the score.

Problems:
There are two of these.
1) What is this number that Excel is giving to me? I have asked multiple people and attempted to find answers on the web, but was unable to find anything.
2) Is there a better way to do this without macros?

Thanks all for the help!
Brandon

 

[Andrew Poulsom]

Here is a layman's explanation of standard deviation:

http://www.robertniles.com/stats/stdev.shtml

In your case I don't think your formula is producing anything meaningful, because the values used are far from normally distributed. In the example below you will see that a person who completes 100% of 100 issues on time has the same standard deviation as the person who completes 75% of 100 issues on time. Column E shows the longhand calculation of standard deviation. The formula in E2 is:

=SQRT(SUM((A2-AVERAGE(A2:C2))^2,(B2-AVERAGE(A2:C2))^2,(C2-AVERAGE(A2:C2))^2)/(COLUMNS(A2:C2)-1))

copied down.

The example shows a possible scoring formula by computing on time issues/total ontime issues and multiplying that by percent on time/total percent on time. I'm not an expert on maths or statistics, but the wieghting looks reasonable. The formula in F2 is:

=((A2*B2)/SUMPRODUCT($A$2:$A$6,$B$2:$B$6)*100)*(B2/(SUMPRODUCT($A$2:$A$6,$B$2:$B$6)/SUM($A$2:$A$6)))

copied down.

Book12
	A	B	C	D	E	F
1	Issues	On Time	Total	StDev	StDev	Score
2	100	100%	430	225	225	49
3	10	100%	430	245	245	5
4	20	50%	430	243	243	2
5	100	75%	430	225	225	28
6	200	50%	430	215	215	25
7	430	69%
Sheet1

 

[PolarBear]

Just curious - are all issues "equal", in the sense that they provide the same challenge? If the guy who gets 10 very difficult things done on time gets ranked well below the guy who gets 100 very easy things done on time, I'd say there's something wrong with your metric.

Also, are there unresolved issues? Is any issue not solved "on time" considered unresolved, or do you have three categories, "on time", "solved but late", and "unsolved"?

If you had a numerical weighting for difficulty, then you could consider something like Score = issues resolved/issues presented * % issues solved on time * average(weights of resolved issues). Of course, this presumes all three things are equal. If on-time is twice as important as the rest, you might need to use a weighted score:

score = resolved/presented + 2*on-time + difficulty

 

[Case250]

Standard deviation in simple language gives you the average variation of each person from the mean. Probably not applicable to what you are after.