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


New Member
Oct 4, 2006
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:

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.

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!

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Andrew Poulsom

MrExcel MVP
Jul 21, 2002
Here is a layman's explanation of standard deviation:

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:


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:


copied down.
1IssuesOn TimeTotalStDevStDevScore


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


Board Regular
Oct 4, 2006
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

Latest member

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
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 "".
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