Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating STDEV for non zeroes
Hi to whom might be of assistance
I am trying to calculate the Standard Deviation for a set of data while excluding zeroes. Ley say, I have the following data set 1, 2, 3, 4, 5, 0 2, 1, 3, 0, 5, 4 0, 2, 4, 6, 8, 10 I can calculate the average of the first line of data by sum(A1:F1) / countif(A1:F1) but I am not sure how to calculate the STDEV while excluding the zeros where ever it may be. Is there a way to do so? Thanks, PJS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating STDEV for non zeroes
Try this
=STDEV(IF(A1:F1<0,A1:F1)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "PJS" wrote: Hi to whom might be of assistance I am trying to calculate the Standard Deviation for a set of data while excluding zeroes. Ley say, I have the following data set 1, 2, 3, 4, 5, 0 2, 1, 3, 0, 5, 4 0, 2, 4, 6, 8, 10 I can calculate the average of the first line of data by sum(A1:F1) / countif(A1:F1) but I am not sure how to calculate the STDEV while excluding the zeros where ever it may be. Is there a way to do so? Thanks, PJS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating STDEV and other Calcs based on another cells value | Excel Worksheet Functions | |||
Can I set the default for "show zeroes" to not show the zeroes? | Setting up and Configuration of Excel | |||
How to determine the STDEV of Avg? | Excel Discussion (Misc queries) | |||
STDEV | Excel Discussion (Misc queries) | |||
STDEV...HELP | New Users to Excel |