[deleted]
Hi there,
I read a few other posts but had much difficulty modifying the formulas to match my needs.
I have two columns, one with different times (i.e. 1:07, 2:25, etc.) and another with a different values that range from 1-200. I already used the averageifs function to find the average for each of the time ranges (0:00-0:59, 1:00-1:59, etc) but need to find a formula to find the corresponding standard deviations.
Thank you in advance!
[deleted]
Last edited by joeu2004; May 13th, 2014 at 01:05 PM.
The standard deviation of what, based on what criteria?
It would help to see your AVERAGEIFS formula in order to fill in missing details. For example, your subject line says there are two criteria, but you mention only one (time range). Perhaps the following paradigm will suffice.
Array-enter the following formula (press ctrl+shift+Enter instead of Enter):
=STDEV(IF(A1:A1000>=TIME(1,0,0),IF(A1:A1000
Add a space after it so it isn't interpreted as the start of a tag.the GUI will not allow me to use the "less than" symbol in that context
The only criterion is time, but I am trying to find the population standard deviation of the second column based on a time range (I though that the upper and lower bound of the time range represents 2 criteria).
The averageif formula that I used is =AVERAGEIFS(B:B,A:A,">=0",A:A,"<0.041667") . I didn't know how to incorporate the time into it, so I used the numerical version for each hour (1:00=0.0416667, for instance).
=stdevp(if(a1:a1000 >= 0, if(a1:a1000 <= --"1:00", a1:a1000)))
The formula MUST be confirmed with Ctrl+Shift+Enter instead of just Enter.
Hmm, I tried the above formula and it is giving me a standard deviation that is different from the one that I would expect. I filtered the time column (column A) for all values within 0:00-0:59, copied and pasted this, and then took the standard deviation of the second column and this standard deviation is different from the one that I got when using the above formula.
shg demonstrates how to modify the formula I presented before as a paradigm. However, I would be careful with the "<=" and "<" conditions. Previously, you said the time ranges were "0:00-0:59, 1:00-1:59, etc". So I would write either <=--"0:59" or <--"1:00".
TIME(1,0,0) and --"1:00" are the same thing. The double-negative (--) converts text to numeric. I use it myself. But it confounds some people.
Two comments, if I may.
First, use limited range references like B1:B1000 instead of full-column references like B:B. The latter usually causes Excel to process 1+ million rows in Excel 2007 and later, and to create 1+ million intermediate arrays in some contexts (not necessarily this one).
Second, use "<1:00" in the AVERAGEIFS conditional parameter instead of "<0.041667". 0.041667 is an approximation of 1:00. In fact, in this case, 0.041667 is about 1:00:00.029. Since that is greater than 1:00, "<0.041667" is effectively equivalent to "<=1:00", which was expressly not your intent. It might skew your results somewhat.
Like this thread? Share it with others