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

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,

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.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Billp" wrote:

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,

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

Best regards,
Dave


"Billp" wrote in message
...
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