finding standard deviation in a group of numbers.
- Thread starter hkoduri
- Start date
so you have a set of numbers between 20 and 90 and you want the standard deviation to be under 60, because that's how i understand your question.
if that's the case I can come up with 2 alternatives:
- add more values that is less than 60 until the standard deviation is below 60
- or change some of the larger numbers in the value set to 20.
if that's the case I can come up with 2 alternatives:
- add more values that is less than 60 until the standard deviation is below 60
- or change some of the larger numbers in the value set to 20.
Mike86
Active Member
or, if you want to calculate the standard deviation of only the values less than 60 in your set:
=SQRT(SUM(((A1:A13)*((A1:A13)<60)-AVERAGEIF(A1:A13,"<"&60)*((A1:A13)<60))^2)/(COUNTIF(A1:A13,"<"&60)-1))
entered using Cntrl-Shift-Enter.
This is the standard deviation for a sample. The population standard deviation doesn't have the "-1" in the divisor.
=SQRT(SUM(((A1:A13)*((A1:A13)<60)-AVERAGEIF(A1:A13,"<"&60)*((A1:A13)<60))^2)/(COUNTIF(A1:A13,"<"&60)-1))
entered using Cntrl-Shift-Enter.
This is the standard deviation for a sample. The population standard deviation doesn't have the "-1" in the divisor.
Mike86
Active Member
hkoduri:
My previous equation works on Excel 2007 and 2010, earlier versions might have issues. The data range / column may be causing challenges. Let's try something:
Assuming your values are in Col A, make the following named range called "Values":
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))
Then, copy the following equation into a cell and hit Cntrl-Shift-Enter:
=SQRT(SUM(((Values)*((Values)<60)-AVERAGEIF(Values,"<"&60)*((Values)<60))^2)/(COUNTIF(Values,"<"&60)-1))
In my tests, this compares well with the Excel function StdDev used on the appropriate sub-set of data.
My previous equation works on Excel 2007 and 2010, earlier versions might have issues. The data range / column may be causing challenges. Let's try something:
Assuming your values are in Col A, make the following named range called "Values":
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))
Then, copy the following equation into a cell and hit Cntrl-Shift-Enter:
=SQRT(SUM(((Values)*((Values)<60)-AVERAGEIF(Values,"<"&60)*((Values)<60))^2)/(COUNTIF(Values,"<"&60)-1))
In my tests, this compares well with the Excel function StdDev used on the appropriate sub-set of data.
99 60
110 94 73 123
136 114 94 113 94 106
142 123 111 81 80 120 89 87
190 223 181 129 65 76 116 107 92
80 180 211 176 173 147 102 75 78 130 132
84 190 200 162 166 114 95 83 88 123 123
141 211 211 200 166 174 139 81 95 147 123
173 239 224 201 183 109 94 128 127
120 112 127 161 167 104 86 101 162
161 161 108 154 124 81 105 161 163
129 114 136 120 85 115 168 169
128 116 93 142 176 176
112 168 184 147
155 161
HELLO GUYS,
THESE ARE THE SET OF VALUES BETWEEN A1:M15, i AM TRYING TO GET STDEV FOR VALUES LESS THAN 121. USING =(SQRT(SUM(((A1:M15)*((A1:M15)<121)-AVERAGEIF(A1:M15,"<"&121)*((A1:M15)<121))^2)/(COUNTIF(A1:M15,"<"&121)-1))) BY MIKE86 i AM GETTING STDEV AS 130.1783,WITH
=Stdev(if(A1:M15<121,A1:M15)) BY iNDIAN IT GIVES STDEV OF 47.52955. WHEREAS IF YOU TAKE MANUALLY EACH VALUE LESS THAN 121 AND TABULATE THEM, i GET A STDEV OF 15.62991. IF I MAKE ALL VALUES INTO A SINGLE COLUMN BOTH FORMULAS ARE WORKING. i HAVE A RANGE OF A1 TO M15. ANY TWEAKS IN THE FORMULAS. THANKS A LOT.
110 94 73 123
136 114 94 113 94 106
142 123 111 81 80 120 89 87
190 223 181 129 65 76 116 107 92
80 180 211 176 173 147 102 75 78 130 132
84 190 200 162 166 114 95 83 88 123 123
141 211 211 200 166 174 139 81 95 147 123
173 239 224 201 183 109 94 128 127
120 112 127 161 167 104 86 101 162
161 161 108 154 124 81 105 161 163
129 114 136 120 85 115 168 169
128 116 93 142 176 176
112 168 184 147
155 161
HELLO GUYS,
THESE ARE THE SET OF VALUES BETWEEN A1:M15, i AM TRYING TO GET STDEV FOR VALUES LESS THAN 121. USING =(SQRT(SUM(((A1:M15)*((A1:M15)<121)-AVERAGEIF(A1:M15,"<"&121)*((A1:M15)<121))^2)/(COUNTIF(A1:M15,"<"&121)-1))) BY MIKE86 i AM GETTING STDEV AS 130.1783,WITH
=Stdev(if(A1:M15<121,A1:M15)) BY iNDIAN IT GIVES STDEV OF 47.52955. WHEREAS IF YOU TAKE MANUALLY EACH VALUE LESS THAN 121 AND TABULATE THEM, i GET A STDEV OF 15.62991. IF I MAKE ALL VALUES INTO A SINGLE COLUMN BOTH FORMULAS ARE WORKING. i HAVE A RANGE OF A1 TO M15. ANY TWEAKS IN THE FORMULAS. THANKS A LOT.