Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mean, median, mode, standard diviation | Charts and Charting in Excel | |||
calculating median and mode | Excel Worksheet Functions | |||
Find the median 3 values | Excel Discussion (Misc queries) | |||
STDEV function on selected values | Excel Worksheet Functions | |||
MEDIAN, STDEV of a range IF another range = specific value | Excel Worksheet Functions |