I need to calculate the standard deviation based on two criteria. Criteria 1: Date Range. The sample must fall between two years. Criteria 2: Sample must be labeled as NV.
I have well over 7000 samples so here is just a very small sample.
For example, I would like to find the standard deviation of all Samples that are labeled as NV between the years 1999 and 1970.
Breakdown
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]...D[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]NV or V[/TD]
[TD]Sample[/TD]
[TD]Year Range[/TD]
[/TR]
[TR]
[TD]1998.5[/TD]
[TD]V[/TD]
[TD].5[/TD]
[TD]1999[/TD]
[/TR]
[TR]
[TD]1998[/TD]
[TD]NV[/TD]
[TD].6[/TD]
[TD]1998[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]1980.6[/TD]
[TD]V[/TD]
[TD].3[/TD]
[TD]1980[/TD]
[/TR]
[TR]
[TD]1979.2[/TD]
[TD]NV[/TD]
[TD].5[/TD]
[TD]1979[/TD]
[/TR]
[TR]
[TD]1974[/TD]
[TD]NV[/TD]
[TD].1[/TD]
[TD]1978[/TD]
[/TR]
[TR]
[TD]1970.1[/TD]
[TD]NV[/TD]
[TD]1.01[/TD]
[TD]1975[/TD]
[/TR]
[TR]
[TD]1960[/TD]
[TD]V[/TD]
[TD]1.09[/TD]
[TD]1960[/TD]
[/TR]
</tbody>[/TABLE]
Here is something that I tried to use, but as a regular formula, it returned a #DIV ! error. As an array it returned a #REF ! error. Note this doesn't have the "NV" criteria. I was just trying to see if the formula would return anything.
=STDEV.S(IF((a$3:a$7023>=d4)*(a$3:a$7023<d3),c$3:c$7023))
Any helpful hints would be appreciated. One would think that Microsoft would have come up with a STDEVIFS function by now.
Thanks a bunch!
I have well over 7000 samples so here is just a very small sample.
For example, I would like to find the standard deviation of all Samples that are labeled as NV between the years 1999 and 1970.
Breakdown
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]...D[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]NV or V[/TD]
[TD]Sample[/TD]
[TD]Year Range[/TD]
[/TR]
[TR]
[TD]1998.5[/TD]
[TD]V[/TD]
[TD].5[/TD]
[TD]1999[/TD]
[/TR]
[TR]
[TD]1998[/TD]
[TD]NV[/TD]
[TD].6[/TD]
[TD]1998[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]1980.6[/TD]
[TD]V[/TD]
[TD].3[/TD]
[TD]1980[/TD]
[/TR]
[TR]
[TD]1979.2[/TD]
[TD]NV[/TD]
[TD].5[/TD]
[TD]1979[/TD]
[/TR]
[TR]
[TD]1974[/TD]
[TD]NV[/TD]
[TD].1[/TD]
[TD]1978[/TD]
[/TR]
[TR]
[TD]1970.1[/TD]
[TD]NV[/TD]
[TD]1.01[/TD]
[TD]1975[/TD]
[/TR]
[TR]
[TD]1960[/TD]
[TD]V[/TD]
[TD]1.09[/TD]
[TD]1960[/TD]
[/TR]
</tbody>[/TABLE]
Here is something that I tried to use, but as a regular formula, it returned a #DIV ! error. As an array it returned a #REF ! error. Note this doesn't have the "NV" criteria. I was just trying to see if the formula would return anything.
=STDEV.S(IF((a$3:a$7023>=d4)*(a$3:a$7023<d3),c$3:c$7023))
Any helpful hints would be appreciated. One would think that Microsoft would have come up with a STDEVIFS function by now.
Thanks a bunch!