Good morning Math Experts, I am having difficulty figuring out how to calculate the standard deviation for a set of data.

Here is the request from my user: Going back three years from today, calculate the yearly average of contacts an officer has with a citizen and the standard deviation of the yearly average across a department.

So I have counted all the contacts in the three year period and counted all the officers that made those contacts averaged that and divided by three to get a single year average:

enter image description here

How can I calculate the standard deviation of this data at the ONE year level? Am I even calculating the one year average correctly?

I feel like maybe I am making this too complicated but don't think the answer the SQL STDEV() function is giving is correct. I am using SQL server query but can use R if that makes it easier.


Separate the query per year first. So for example, call $e_1$ the average number of encounters in the last year (TotalContants/NumberOfOfficers), $e_2$ the average number of encounters from one year ago to two years ago, ...

You will then have a list of values $e_1, e_2, \cdots$, the calculate the standard deviation of that

$$ \sigma^2 = \frac{1}{n}\sum_{k=1}^n (e_k - \overline{e})^2 $$

in your case $n = 3$. The issue here is that three points are usually not enough to actually describe what's happening. My suggestion is to define the quantity

$$ e_\delta(n) = \frac{\text{Total contancts in the period }(n\delta - 1{\rm yr}, n\delta)}{\text{Number of officers in the period }(n\delta - 1{\rm yr},n\delta)} $$

So for example you can set $\delta = 1~{\rm month} = 1/12~{\rm year}$, and $e_{1/12}(0)$ is the average number of encounters in the last years, $e_{1/12}(-1)$ is the number of encounters in one year measured from 1 month ago, $e_{1/12}(-2)$ ... Doing it this way you get a sample of 24 points

$$ \sigma^2 = \frac{1}{n}\sum_{k=0}^{n}(e_{1/12}(-k) - \overline{e}) $$

with $n=24$


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.