Getting the standard deviation of a range after using TRIMMEAN

[timvdbekerom]

Hello there,

I have been trying to resolve an Excel-problem without success. For a data range (X4:AI4) I have used TRIMMEAN with the following formula, but I would like to know the standard deviation of this range:
=TRIMMEAN(X4:AI4;0,2)

Thanks in advance,
Tim

[daddylonglegs]

X4:AI4 is 12 cells so by using TRIMMEAN with 0.2 you will exclude just the smallest and highest values, assuming the range is fully populated that's the equivalent of taking the average without the smallest and 12th smallest, i.e.

=AVERAGE(SMALL(X4:AI4;{2;3;4;5;6;7;8;9;10;11}))

so you can do the same with Standard Deviation by replacing AVERAGE with STDEV, i.e.

=STDEV(SMALL(X4:AI4;{2;3;4;5;6;7;8;9;10;11}))

If you want a formula to cope with variable sized ranges or for when the cells are only partially populated then you need a slightly more complex version.....

[timvdbekerom]

Hello Daddylonglegs,

Thank you for your reply, but indeed I need a formula which is able to cope with varying sample sizes and trimmed values (trimmean 0.2 / 0.4)

[daddylonglegs]

OK, more generic, then - this "array formula" will give you STDEV of the trimmed Range1, using Percent value (to trim)

=STDEV(SMALL(Range1;ROW(INDIRECT(INT(COUNT(Range1)*Percent/2)+1&":"&COUNT(Range1)-INT(COUNT(Range1)*Percent/2)))))

confirmed with CTRL+SHIFT+ENTER

[timvdbekerom]

Hello,

Thank you, I still have one question concerning the formula stated above. Generally when you use TRIMMEAN you will trim the lowest AND biggest value(s), depending on the trim percentage. As it seems to me this hasn't been taken into account in the formula above? I just need to be sure of this.

Thanks in advance!!

[daddylonglegs]

Yes, it's designed to use exactly the same values as TRIMMEAN does for an average. You can test that by using AVERAGE in place of STDEV in that last formula I suggested (still "array entered") and you should get the same result as TRIMMEAN for the same values.

A small example.

If you have 24 values in Range1 and Percent set to 0.4 the TRIMMEAN trims 20% of the values off the top and bottom, in this case 20% of 24 is 4.8 so that's rounded down to 4 and 4 values are trimmed off the top and the bottom.

My suggested formula works out that the remaining values are the 5th smallest through to the 20th smallest, and calculates the STDEV of those values only (excluding the top 4 and bottom 4).

Make sure you use CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula