Standard deviation based on 2 criteria

[dolls]

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!

 

[joeu2004]

[deleted]

 

[shg]

The standard deviation of what, based on what criteria?

 

[joeu2004]

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.

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<time(2,0,0),
<=TIME(1,59,0),
<time(2,0,0),
IF(B1:B1000>=10,IF(B1:B1000<=190,B1:B1000)))))

That calculates the sample std dev of values in B1:B1000 for 1:00<=time<=1:59 and 10<=value<=190, where time is in A1:A1000.

(Use STDEVP if you want the population std dev. See the Excel help pages for the difference, if you don't know.)

Note: This forum's GUI is mangling my formula. I really want to write "less than TIME(2,0,0)" for the condition 1:00<=time<2:00. But the GUI will not allow me to use the "less than" symbol in that context. </time(2,0,0),
</time(2,0,0),

 

[shg]

the GUI will not allow me to use the "less than" symbol in that context

Add a space after it so it isn't interpreted as the start of a tag.

 

[dolls]

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<time(2,0,0),
<=TIME(1,59,0),
<time(2,0,0),
IF(B1:B1000>=10,IF(B1:B1000<=190,B1:B1000)))))

That calculates the sample std dev of values in B1:B1000 for 1:00<=time<=1:59 and 10<=value<=190, where time is in A1:A1000.

(Use STDEVP if you want the population std dev. See the Excel help pages for the difference, if you don't know.)

Note: This forum's GUI is mangling my formula. I really want to write "less than TIME(2,0,0)" for the condition 1:00<=time<2:00. But the GUI will not allow me to use the "less than" symbol in that context. </time(2,0,0),
</time(2,0,0),

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

 

[shg]

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

 

[dolls]

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

 

[joeu2004]

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

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.

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

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.

 

[joeu2004]

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.

Our postings crossed on the "ether". See my previous response regarding "<=--1:00" [sic, shg] v. "<--"1:00" (your intent). Does that resolve the difference that you see?