STDEV to ignore empty cells, but it's within an existing array.

[Jammers]

Greetings Excel gurus,

A common question with stdev functions is "how do I get stdev to ignore empty cells or zero entries". I've seen answers to this, however, my stdev is already within an array, and I'm having trouble getting the function below to ignore empty cells or zero values.

Any suggestions would be greatly welcomed.

{=STDEV.S(IF(L9:L27="Yes",N9:N27))}


Thanks in advance.

Jammers

 

[AhoyNC]

Try:

[TABLE="width: 336"]
<colgroup><col width="336"></colgroup><tbody>[TR]
   [TD="width: 336"]=STDEV.S(IF(L9:L27="Yes",IF(N9:N27<>0,N9:N27)))[/TD]
 [/TR]
</tbody>[/TABLE]

 

[Jammers]

Thanks AhoyNC, that works perfectly!

 

[AhoyNC]

You're welcome. Thanks for the feedback.