#Using mean and standard deviation excel how to#

If, on the other hand, you want to calculate a STDEV but exclude those values that would be excluded by TRIMMEAN, then you could use the DSTDEVP function, and set your criteria to exclude the largest and smallest number of entries So, maybe (confirmed with CTRL+SHIFT+ENTER) =SQRT(SUM((Values-AVERAGE(Values))^2)/COUNT(Values)) The STDEVP formula, for example, could be represented by this formula: I don't know what that result would mean, however. If you want to calculate the standard deviation, using the entire population, but based on the TRIMMEAN average rather than the actual population average, I suppose you could substitute the TRIMMEAN average for the population average in the underlying standardĭeviation formula. It's not clear to me exactly what you want to do. It seems that if one wants to strip the outliers before calculating a STDEV there is no easy way of doing so. The STDEV should trim (invoke the TrimMean function under the covers) to calculate the mean. TrimMean value, which is the same except for the outliers.Īfter some thought, it appears that STDEV will only calculate based on the values being presented to it and there is no way to specify a mean value, however it is derived, from which a STDEV value can be calculated, or failing that, being able to specify

#Using mean and standard deviation excel how to#

My real purpose of attempting to pass the already calculated Average is that once I understood how to do that (assuming that it could be done, which I couldn't do) I could just substitute the When I already had it and just want to pass that value to STDEV. So, why go through the overhead of recalculating the average As part of what I am doing I already had calculated the Average from a needed previous calculation. STDEV will take either a list of values (up to 30) to average or an array of values to average. OK Dana, thanks for your response, and I was a little obtuse in my description of the problem, but will clarify. Just guessing, but it sounds like you need a custom vba function that will remove the High and Low from an array of values, then apply the StDev formula to the remaining values. Off hand, I don't think one can easily use the value of 10 ' Only', and arrive at the correct StDev.

I don't quite understand the question.īoth have the same Average value of 10, but different StDev.

I also attempted to use the results of the Average function as a direct input to the STDEV I've seen other convoluted solutions of using trim like calculations as input but could not find any reference to using it the way I described or that it doesn't work. I also attempted to use the results of the Average function as a direct input to the STDEV function thinking that if I could get to work I would use the same syntax. My question is how (if in fact it can be done) can the resultant value be used in calculating a standard deviation? I've attempted various ways of using it as a direct input to the STDEV function and either get a zero value or a divide by zero error. The TrimMean function will strip off a user specified percentage of entries from the top and bottom values from say an array of values, the purpose of which is to eliminate the outliers when calculating the mean (average). I've looked all over the web at the various expert Excel sites for the answer to this question with no results.