Getting correct results from TRIMMEAN

[BruceP]

I have used TRIMMEAN in the past and thought I had the correct results from it. Only yesterday I was working on some data and wanted to use TRIMMEAN to always remove a fixed number of data points from arrays that are variable in size, i.e. one might have 257, the next 150.

So, to create the percentage part of the TRIMMEAN function I had a small bit of math: to remove 10 data points (top five & bottom five) I had 1-((X-10)/X) where X is the total number of data points. However, this didn't return the correct result and I kept on getting the average. If I worked out what the percentage was and entered that directly into the function, either as a decimal (i.e. 0.2) or a number formatted as percentage (i.e. 20%), then the correct result was obtained. However, if the math I typed was, for example, 2*0.1, the correct result was obtained.

The only way I could get the math version to work was to wrap it in the TEXT function, i.e. TEXT(1-((X-10)/X),"0.00%"), and then it returns the correct result.

So, I've managed to fix this but am totally freaked out why the function will not except some maths that returns a percentage/figure that one would expect to work.

Can anybody shed some light on this?

My thanks in advance.

p.s. I tried to add some data from a sheet I worked on with this using MrExcel HTML maker but it didn't seem to work. If you want some examples of what I was working with then please let me know

 

[StephenCrump]

The only way at the moment I can envisage this happening is if your formula is say:

=TRIMMEAN($A$1:$A$100,B10) where B10 contains the formula =1-(X-10)/X.

Then it would be possible, perhaps in copying the formula, to have the B10 reference point to an incorrect blank cell, in which case TRIMMEAN would return the same as AVERAGE.

Alternatively, perhaps your formula is say:

=TRIMMEAN($A$1:$A$100,1-($B$10-B11)/$B$10) where B10 contains the count of values e.g. 257, and B11 contains the number to be excluded, i.e. 10.

If in copying the formula, the B11 reference points to a blank cell, the same thing will happen.

(I think the TEXT() function thing is a red herring ...

TRIMMEAN(A1:A8,TEXT(0.2,"0.00%"))will return the same as TRIMMEAN(A1:A8,0.2) because Excel recognises the text value as a number. If it's working for you, it suggests that in that particular case you had the right number to start with).

 

[BruceP]

Thanks for looking into it.

Much appreciated