calculating average with multiple criteria and removing outliers

[bercier]

Good afternoon,

I have built with power query a list of payments made for the last 4 years to employee's. I am trying to calculate the bi-weekly average salary of those 10 000 employee's based on 2 criteria's. For each employee / fiscal year, I want to find the average bi-weekly payment. I want to remove the outliers top 10% and bottom 10% in column H for each combo of employee / fiscal year.

Column A - Criteria 1 employee number: 1, 2, 3...10 000
Column F - Criteria 2 fiscal year of the payment : 2016, 2017, 2018,...)

Column H - Bi-weekly payment amount received

Thank you
Sylvain

 

[joeu2004]

IMHO, you have not provided sufficient details for us to provide a turnkey solution.

Also, the "top 10% and bottom 10%" are not necessarily "outliers", in the statistical sense.

That said, take a look at TRIMMEAN. It does remove the top and bottom x% of the data.

Perhaps you would array-enter (press ctrl+shift+Enter instead of just Enter) a formula of the following form:

=TRIMMEAN(IF($A$1:$A$10000=C1, IF($F$1:$F$10000=C2, $H$1:$H$10000)), 20%)

PS.... I am not familiar with Power Query, so I do not know if and how such formulas would fit with its use.

 

[bercier]

IMHO, you have not provided sufficient details for us to provide a turnkey solution.

Also, the "top 10% and bottom 10%" are not necessarily "outliers", in the statistical sense.

That said, take a look at TRIMMEAN. It does remove the top and bottom x% of the data.

Perhaps you would array-enter (press ctrl+shift+Enter instead of just Enter) a formula of the following form:

=TRIMMEAN(IF($A$1:$A$10000=C1, IF($F$1:$F$10000=C2, $H$1:$H$10000)), 20%)

PS.... I am not familiar with Power Query, so I do not know if and how such formulas would fit with its use.

 

[bercier]

thank you so much it works!! now just got to find a way to do it on a much larger data set.