find for non zero values in a column Median,Mode,STDEV

[Billp]

Hi,
I have a column Range J2:J300. I have seen from other posts the method of
getting the average(Mean) is
=SUM(J2:J300)/COUNTIF(J2:J300,">0")

I have tried for the STDEV
=STDEV(IF(J2:J300=0,"",J2:J300))
and get #VALUE!

The sheet will have various ranges imported (It is going to be used for
determining the statistics of a packaging machine) within the main range of
J2:J300
If other users are going to have this as a template is there a way to obtain
the Median, Mode, And StDEV of the column taking only positive numbers and
ignoring zero values?

Thank You
Bill

 

[macb]

Hi,
I have a column Range J2:J300. I have seen from other posts the method of
getting the average(Mean) is
=SUM(J2:J300)/COUNTIF(J2:J300,">0")

I have tried for the STDEV
=STDEV(IF(J2:J300=0,"",J2:J300))
and get #VALUE!

The sheet will have various ranges imported (It is going to be used for
determining the statistics of a packaging machine) within the main range of
J2:J300
If other users are going to have this as a template is there a way to obtain
the Median, Mode, And StDEV of the column taking only positive numbers and
ignoring zero values?

Thank You
Bill

Hi:

Try this:

=STDEV(IF(D1117<>0,$D$11:$D$17)) - Array Entered - use (Ctrl+Shift
+Enter) to enter the formula


Regards


Mac

 

[Shane Devenshire]

Hi,

You should enter the formula(s) as arrays - press Shfit+Ctrl+Enter.

You can also simpliy the formula to

=STDEV(IF(J2:J300,J2:J300,""))

Also an array.

 

[Bassman62]

Hi,

To include only positive numbers in the sample;
=STDEV(IF(J2:J300>0,J2:J300,""))
To commit as an array formula hold Ctrl & Shift and press Enter.

Best regards,
Dave