Excel Median of even number of values with a condition

Question

When I calculate the median of even numbers for e.g 1,2,3,4,5,6,7,8. I want the return value to be 5 i.e. the higher value of the two middle values and not the average of 4 & 5. Can someone help me with this?

Answer 1

The LARGE function, COUNT function, and TRUNC function should all be used in conjunction to achieve this, in my opinion. For instance, using the formula, even though it technically isn't the median, you should be able to find the desired result if the numbers you are dealing with are in cells A1 through A8.

=LARGE(A1:A8,TRUNC((1+COUNT(A1:A8))/2))