Trimmean function does not seem to work (with attached example)

[Micdis]

Hi all,

I did a trimmean function and have a very strange result. My trimmean at the 50th percentile (cell b79) is lower than my trimmean at the 25th percentile (cell b78). Which is impossible since I only have have positive number and I know from another analysis the estimated results (see attached file). It drives me nuts, if somebody can have a look at it I would really appreciate it.

Thank you for your help,

[Micdis]

Nobody can crack this problem?

[daddylonglegs]

Can you describe what you are trying to do in words? I don't think TRIMMEAN does what you think it does. It removes "outliers" from the data, equally from the top and the bottom, so TRIMMEAN(data,0.75) actually ignores the top 37.5% of the data and the bottom 37.5% and averages the middle 25%, so the result might be lower or higher than TRIMMEAN(data,0.5), one can't know which.

If you want to average the bottom 25% of the values then perhaps try this formula

=AVERAGE(IF(ISNUMBER(A$105:A$475),IF(B$105:B$475<=QUARTILE(IF(ISNUMBER(A$105:A$475),B$105:B$475),1),B$105:B$475)))

confirmed with CTRL+SHIFT+ENTER