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

bgromis

New Member
Joined
Oct 4, 2006
Messages
1
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
ABCDEF
1IssuesOn TimeTotalStDevStDevScore
2100100%43022522549
310100%4302452455
42050%4302432432
510075%43022522528
620050%43021521525
743069%
Sheet1
 

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215
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

Board Regular
Joined
Oct 4, 2006
Messages
54
Standard deviation in simple language gives you the average variation of each person from the mean. Probably not applicable to what you are after.
 

Forum statistics

Threads
1,136,260
Messages
5,674,695
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top