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

#### bgromis

##### New Member
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 fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Andrew Poulsom

##### MrExcel MVP
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
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
Standard deviation in simple language gives you the average variation of each person from the mean. Probably not applicable to what you are after.

Replies
3
Views
299
Replies
0
Views
492
Replies
2
Views
861
Replies
4
Views
304
Replies
3
Views
1K

1,171,825
Messages
5,877,764
Members
433,285
Latest member
iAbdullah

### 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?

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