Calculating standard deviation

New Contributor

I wondering if there is a way to calculate the STD with data that if the value is 0 then its just not there. For example if the data set was 8,9,4, and 6 but there was a total number of 20 data points. The others are to be considered 0. Is there a way to calculate the STD without having to input all the 0's? I planned on using the STDEV.S function.

1 Reply

@Rangerrick22 

I'm not clear on which portion of your data is considered "other" and must not be included in the standard deviation calculation.  I'll provide you with examples and I think you can run with it.

 

The numbers are in A1:A10

 

One Criteria:
=LET(rng,$A$1:$A$10,STDEV.S(IF(rng<>0,rng)))

 

Two Criteria (Acts as an AND):

=LET(rng,$A$1:$A$10,STDEV.S(IF(rng<>0,IF(rng<30,rng))))