Do a median without using the mean

[Gaz_m2k5]

Hello,

I have 2 columns of data, call them x and y.

Ideally I want to find the average of x, and then display the corresponding y value.

Here is a small sample of data.

Please Login or Register  to view this content.

I tried using MATCH, but this isn't very reliable as the criteria for matching the values (less than) isn't great. It rarely finds the correct corresponding y value.
Using an exact match would be good, but if I do a mean average of the x data, it might return a value which ISN'T in the table, and therefore an exact match would not be found.

So I decided to use the median instead, which should be accurate enough for what I want.

But the median averages results sometimes if there is an even number of data. eg. The median of 3, 4, 5 is 4. BUT the median of 3, 3, 4, 5 is 3.5. Trying to do an exact match with look up value of 3.5 in that data would not work, since 3.5 is not part of that data.

Is there a way to get the median to select 1 data point above or below the median, rather than doing a mean average?

[Tank997]

How about something like this?

Please Login or Register  to view this content.

[ChemistB]

Tanks idea only works if you put your values in order first (looks like they are not).
If you are stuck on Median, try

for low end of median
=VLOOKUP(IF(ISEVEN(COUNT(A2:A20)), MEDIAN(A2:A20, MIN(A2:A20)), MEDIAN(A2:A20)),2,FALSE)

for high end of median
=IF(ISEVEN(COUNT(A2:A20)), MEDIAN(A2:A20, MAX(A2:A20)), MEDIAN(A2:A20))
Does that work?

[Gaz_m2k5]

Found a simpler way to do it:

Please Login or Register  to view this content.

Counts the number of data values, halves that and finds the xth largest value.

Thanks for the help!

[ChemistB]

Clever!