• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Median

Hi,

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

Attachments

  • Conditional Median.xlsx
    17.4 KB · Views: 6
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))


66259

Regards
Bosco
 

Attachments

  • Conditional Median.xlsx
    18.1 KB · Views: 3
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.
66263

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.
 
Back
Top