Higher Rank Median Value Formula #2

[j.petrut]

Hi, I posted yesterday seeking the higher rank value of the median of an even numbered data set...

(FROM PREVIOUS POST)

2, 2, 6, 8, 8, 10 ---> The median value will technically be 7, but I am looking for a formula to spit out the 8 value instead, the higher rank data of the two values that make up the median; basically looking for a single formula that will pull the higher rank data value when there is an even amount of data and calculate the median value as ordinary when there is an odd amount of data.



I was given to formulas below as examples..

=IF(ISEVEN(COUNTA(A2:A7)),MIN(IF(MEDIAN(A2:A7)<=A2:A7,A2:A7)),MEDIAN(A2:A7))
and
=AVERAGE(LARGE(A1:A10,INDEX({0,1;1,1},1+ISODD(COUNT(A1:A10)),)+COUNT(A1:A10)/2))

Both worked great. Now I am looking for this same concept across a bigger range of data associated with different ID's..

EXAMPLE -->

Capture.PNG


The true median for ID:A with values 1, 2, 5, 8 will be 3.5 --> But I am looking for the value 5
The true median for ID:B with values 3, 4, 6, 7 will be 5 --> But I am looking for the value 6

I have the formulas above as well as one to pull a median for the an odd numbered data set (below) and am seeking an equation to combine the equations to give me a single formula if possible!

={MEDIAN(IF(idrange=A, values)) --> takes the median of the values associated with ID A.

Help on combining the two equations appropriately would be tremendous!! Thank you in advance to anyone offering input!

NOTE: the data is not in ascending order and need the equation to work without having to sort the data by ID if possible!

[XOR LX]

Hi again,

Rather that a picture, could you post an actual workbook?

Regards

[j.petrut]

EXAMPLE.xlsx

Here is the excel sheet.