Use IF statement in STDEV function?

[finalmike]

Is there a way to conditionally include a value in a standard deviation calculation without having to type it out manually? I have the following spreadsheet which shows what I mean. The only way I can get it to work is to type the standard deviation calculation out manually which adds a lot of processing time.

Thanks

[joeu2004]

Array-enter (press ctrl+shift+Enter instead of just Enter) the following formula:

=IFERROR(STDEV(IF(I5:I9="yes",H5:H9)),"")

The essential formula is the red-highlighted part. The IFERROR wrapper covers the case when your IF condition selects one or zero rows.

BTW, you have the wrong formula H20. It should be =STDEV(H5,H7:H9).

And you should consider using STDEVP instead of STDEV.

STDEVP is the "true" (actual) std dev. STDEV is an estimated std dev of a larger "population" (data), assuming the given data is a representative sampling.

[finalmike]

Thanks. I will try it out. I made the example sheet in about 60 seconds cause I had to go so no surprise on the error. I'm no statistician, but isn't the STDEV more accurate in this case because you could have infinite replicates, so the population is actually larger than what I'm looking at?

[Glenn Kennedy]

STDEVP should be used ONLY when you have sampled the entire population (what is the SD of the weight of 100 cows in a 100 cow herd) whereas STDEVS should be used when you have taken as sample (what is the SD of 25 cows in a 100 cow herd). Given that you are including the possibility of excluding some values, for some unspecified reason (statistically justifiable??), you should use STDEVS.

So far as I know....

[finalmike]

I'm not sure but for my 12 years in industry, I've always seen STDEV used by everyone, so I need to do more research as to formulate a convincing argument if that's wrong.

I don't think I even have a known population , because the replicates could be infinite.

[Glenn Kennedy]

As far as I remember STDEV = STDEVS. It's just an outdated Excel function that is still included for the purposes of backward compatability. So join the new age and switch to STEVS.

[joeu2004]

isn't the STDEV more accurate in this case because you could have infinite replicates, so the population is actually larger than what I'm looking at?

With the new information, yes.

On the other hand, if by "replicates", you mean you have G5 #data with the value in H5, G6 #data with the value in H6 etc, you might need to calculate a weighted std dev. There is no Excel function to do that simply. And it's doubly complicated because you want to include data conditionally.

[finalmike]

I don't really understand when you would use STDEVP because I figure you can pretty much always have more replicates.

[Glenn Kennedy]

I don't really understand when you would use STDEVP because I figure you can pretty much always have more replicates.

No... re-read post 3. If you are sampling an entire population ...

also read here:

https://statistics.laerd.com/statist...-deviation.php