Search code examples
arraysexcelexcel-formulaconditional-operatormedian

Weighted Median If in Excel

I have an Excel data tab that has the following columns: A: County Name B: Hotel Name C: # of Rooms in the Hotel D: Year Hotel Was Constructed

In my analysis tab, I have the following columns: A: County Name B: Median Age of a Hotel Room

I am trying to find the median age of a "hotel room" in each county. In Excel parlance, I am trying to do a weighted MEDIAN IF - take the median if the county name matches weighted by the number of rooms - so if there are three hotels in the county, one built in 1900 w 100 rooms, one built in 1975 with 50 rooms, and one in 2000 with 200 rooms, the value returned should be 2000. Any help is very much appreciated!!!

Solution

  • You'll need a few auxiliary columns: enter image description here

    Prepare them in the following order:

    Column E to keep track of how many rooms this hotel and other hotels that are in the same county and were constructed earlier have:

    E2 = SUMIFS($C$2:$C$15,$A$2:$A$15,"="&A2,$D$2:$D$15,"<="&D2)

    Column I and J to store how many rooms this county has, and half of that

    J2 = SUMIFS($C$2:$C$15,$A$2:$A$15,"="&I2)
K2 = J2/2

    Column F for how far E is to K, and median occurs at the first hotel that passes the mid point.

    F2 = E2-VLOOKUP(A2,$I$2:$K$4,3,0)

    Column L to find the minimum positive value that surpasses the midpoint:

    {L2 = MIN(IF(($A$2:$A$15=I2)*($F$2:$F$15>=0),$F$2:$F$15,""))}

    and finally Column M to look up the year from I and L against G=A&F:

    M2 = INDEX($D$2:$D$15,MATCH(I2&L2,$G$2:$G$15,0))