Trimmean, averageifs, and countifs

[stwp86]

Hey Everyone . . .

I have the following AVERAGEIFS formula in my workbook: =IFERROR(AVERAGEIFS(Data!$A:$A,Data!$J:$J,"="&CONCATENATE(TRIM($B$3)," - ",TRIM(Orders!$C3)),Data!$K:$K,">="&Orders!$H$1,Data!$K:$K,"<="&Orders!$I$1),0)

The purpose is to average a number set if a string matches and the specified column date falls between another given date.

What I would like to do, is integrate the TRIMMEAN function into the AVERAGEIFS function, if possible. Or, do something similar where the top 5% and/or the bottom 5% are removed from the average calculation.

I also would need to do something similar to this function: =COUNTIFS(Data!$J:$J,CONCATENATE(TRIM($B$3)," - ",TRIM(Orders!$C3)),Data!$K:$K,">="&Orders!$H$1,Data!$K:$K,"<="&Orders!$I$1)

This function gives me the transaction count for the matching string between the same date.

Basically, I have some outliers at the top and bottom, and we have concluded that hacking 5% from the top and bottom cleans the data enough for our purposes, so we dont want to do the IQR method.

Any help would be greatly appreciated.

Thanks!

 

[stwp86]

Thoughts on this anyone? Been wracking my brain over it for a while . . .

Hey Everyone . . .

I have the following AVERAGEIFS formula in my workbook: =IFERROR(AVERAGEIFS(Data!$A:$A,Data!$J:$J,"="&CONCATENATE(TRIM($B$3)," - ",TRIM(Orders!$C3)),Data!$K:$K,">="&Orders!$H$1,Data!$K:$K,"<="&Orders!$I$1),0)

The purpose is to average a number set if a string matches and the specified column date falls between another given date.

What I would like to do, is integrate the TRIMMEAN function into the AVERAGEIFS function, if possible. Or, do something similar where the top 5% and/or the bottom 5% are removed from the average calculation.

I also would need to do something similar to this function: =COUNTIFS(Data!$J:$J,CONCATENATE(TRIM($B$3)," - ",TRIM(Orders!$C3)),Data!$K:$K,">="&Orders!$H$1,Data!$K:$K,"<="&Orders!$I$1)

This function gives me the transaction count for the matching string between the same date.

Basically, I have some outliers at the top and bottom, and we have concluded that hacking 5% from the top and bottom cleans the data enough for our purposes, so we dont want to do the IQR method.

Any help would be greatly appreciated.

Thanks!