Average with Multiple condition

[msharma864512]

Hi,

I am trying to write a formula which will calculate average with below mentioned approach:
> First it will exclude negative values
> After excluding negative values, it will also exclude outliers from the remaining value
For e.g.,

if the data set contains: -10,-15,-20,-5,-1,-100,-200,0,2,5,7,8,10,52,35,35,52,52,100,150,250,250

it will first exclude negative values and will also exclude outliers from the remaining list: 0,2,5,7,8,10,52,35,35,52,52,100,150,250,250

You can exclude 4 values from top and 4 from bottom in the excel.

Please write the formula in cell B260.

 

[bosco_yip]

In B260, array formula ( confirmed enter with SHIFT+CTRL+ENTER)

=TRIMMEAN(IF(B2:B258>0,B2:B258),4/COUNTIF(B2:B258,">0"))

Regards
Boco

 

[msharma864512]

Thank You Very Much Bosco, it was very helpful