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

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

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

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

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