Conditional Median

[msharma864512]

Hi,

I want to calculate median by excluding top and bottom values (But the median should be calculated for the respective sectors only)

 

[bosco_yip]

Try........

1] Using "Remove Duplicates" built-in function to create a "Sector" unique list in D3:D9

2] In E3, enter array formula (Ctrl+Shift+Enter) copied down :

=TRIMMEAN(IF($A$3:$A$340=D3,$B$3:$B$340),2/COUNTIF($A$3:$A$340,D3))




Regards
Bosco

 

[Peter Bartholomew]

I am using Office 365 so, for me, the way of extracting a unique list of sectors is
= SORT( UNIQUE( sector ) )
and the median is given by
= MEDIAN( IF( sector=@selectedSector#, PPE_Sales ) )
where @selectedSector# is a relative reference to the entry on the same row of the list of distinct sectors, determined by implicit intersection.


If you really do require the median and not the mean, then excluding the top and bottom values makes no difference; the middle term is still in the middle. If you want a trimmed mean then @Bosco's formula should do it.

 

[msharma864512]

Try........

1] Using "Remove Duplicates" built-in function to create a "Sector" unique list in D3:D9

2] In E3, enter array formula (Ctrl+Shift+Enter) copied down :

=TRIMMEAN(IF($A$3:$A$340=D3,$B$3:$B$340),2/COUNTIF($A$3:$A$340,D3))


View attachment 66259

Regards
Bosco

Thanks Bosco for the help