Trimmean and averageifs

[cgsierra]

Hello, I'm looking to combine the functionalities of the trimmean and averageifs functions. Can you help me with an excel formula that allows me to get the average of a set of data using multiple criteria (reason why I need averageifs function) but only for data excluding outliers (reason why I need trimmean function)?

 

[Aladin Akyurek]

Care to post a small sample along with the conditions which must hold?

 

[cgsierra]

Care to post a small sample along with the conditions which must hold?

Absolutely,
A5:A4000 has the name of the hospitals, C5:C4000 has the state where each hospital is located, F5:F4000 has the hourly rate paid at each hospital. The conditions would be that the average rate excludes the rates for hospital name ABC (there are multiple hospitals with the same name) from column A, that the average rates include the rates only for state MA from column C, and that the average rate excludes those rates with value zero from column F. I want to exclude the top 10% of the data points and the bottom 10% of the data points.
thank you

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=TRIMMEAN(IF(1-($A$5:$A$4000="abc"),IF($C$5:$C$4000="MA",IF(ISNUMBER(1/$F$5:$F$4000),$F$5:$F$4000))),10%)

 

[cgsierra]

Control+shift+enter, not just enter:

=TRIMMEAN(IF(1-($A$5:$A$4000="abc"),IF($C$5:$C$4000="MA",IF(ISNUMBER(1/$F$5:$F$4000),$F$5:$F$4000))),10%)

Thank you. Where in the formula are the zero values excluded?
Also, what if I wanted to add another contraint to this formula where column D4:D4000 contain name of doctors and I wanted doctor John Doe excluded from the average.
I may have to add other contraints so an alternative to nested if statements would be great.

thank you kindly

 

[Aladin Akyurek]

Thank you. Where in the formula are the zero values excluded?
Also, what if I wanted to add another contraint to this formula where column D4:D4000 contain name of doctors and I wanted doctor John Doe excluded from the average.
I may have to add other contraints so an alternative to nested if statements would be great.

thank you kindly

The ISNUMBER bit does that: Since division by 0 is not a number, ISNUMBER will return FALSE, otherwise TRUE. The as TRUE qualified numbers will be the subject of TRIMMEAN.

IF is the best way to filter for conditions that hold...

Control+shift+enter:

=TRIMMEAN(IF(1-($A$5:$A$4000="abc"),IF($C$5:$C$4000="MA",IF(1-($D$5:$D$400="john doe"),
     IF(ISNUMBER(1/$F$5:$F$4000),$F$5:$F$4000)))),10%)

Note that an expression like 1-(range="x") is equivalent to range<>"x".

 

[cgsierra]

The ISNUMBER bit does that: Since division by 0 is not a number, ISNUMBER will return FALSE, otherwise TRUE. The as TRUE qualified numbers will be the subject of TRIMMEAN.

IF is the best way to filter for conditions that hold...

Control+shift+enter:

=TRIMMEAN(IF(1-($A$5:$A$4000="abc"),IF($C$5:$C$4000="MA",IF(1-($D$5:$D$400="john doe"),
     IF(ISNUMBER(1/$F$5:$F$4000),$F$5:$F$4000)))),10%)

Note that an expression like 1-(range="x") is equivalent to range<>"x".

Perfect! Thank you very much for your help!

 

[Aladin Akyurek]

Perfect! Thank you very much for your help!

You are welcome. Thanks for the update.