STD Deviation Discrepancy

[Argento_Nuclear]

I am trying to find the standard deviation for data presented in different ranges and I am currently using the following equation:
=+STDEV.S(IF((e7a1!A12:A1010>=Primary!K$5)*e7a1!A12:A1010<=Primary!K$6);INDEX(Table1;0;MATCH($D19;Table1[#Headers];0)))

The reason for the formatting is that its for data across a time span based on a column name.

However, I am not getting the right answer out of this as when I compare it to:
=STDEV.S(e7a1!JM49:JM69)

Using the second option where I am manually selecting the same range I am trying to select using equation 1, I am getting the correct answer where as using equation 1 I do not.

I would appreciate the help.

Thanks.

 

[joeu2004]

I suspect that you want to write:

=STDEV.S(IF((e7a1!A12:A1010>=Primary!K$5)*(e7a1!A12:A1010<=Primary!K$6);INDEX(Table1;0;MATCH($D19;Table1[#Headers];0)))

The key is the addition of the red-highlighted left-parenthesis, balanced by removing a right-parenthesis at the end.

Also note the omission of the "+" after "=". It serves no useful purpose.

You might consider STDEV.P instead of STDEV.S. STDEV.P is the actual std dev of the data. STDEV.S is the approximate std dev of a larger "population", assuming that your data is a "sample".

Lastly, you might consider wrapping IFERROR around the STDEV function, in case there is not at least two rows of data that meet the conditions. Something like:

=IFERROR(STDEV.S(IF((e7a1!A12:A1010>=Primary!K$5)*(e7a1!A12:A1010<=Primary!K$6);INDEX(Table1;0;MATCH($D19;Table1[#Headers];0))), "")