I have say, i occurrences of the value x, j occurrences of the value y, k occurrences of the value z, (and so forth).

I'm looking for a way to calculate the median of these occurrences in Excel without having to list out every single occurrence over hundreds of rows (in order to use Excel's MEDIAN function.)

Is there a mathematical formula I could adapt for Excel that would allow the use of how many occurrences of each value occur, rather than manually listing every occurrence out?


Yes. Calculate the total number of values you have. Now, create a cumulative number of values column, starting from the lowest value to the highest. Name the cells that contain the cumulative values and the actual values as a named "range" (e.g., "Data"). Then use an IF function:

IF(N even, AVERAGE(VLOOKUP(N/2,Data,2,TRUE),VLOOKUP(N/2+1,Data,2,TRUE)),VLOOKUP((N/2 + .5,Data,2,TRUE)).

This will look up the Nth value in the list, but not all numbers will be present, so the TRUE will allow approximate matches, specifically it will look up the greatest lower bound of positions. For example, if the median is the 5th value, but it falls in the middle of a string of the value 9, then it will chose 9 as the median.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.