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 |
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 |
All times are GMT +1. The time now is 10:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com