2

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?

Dave
  • 121
  • 1

1 Answers1

0

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.