I'm working with age data. Age groups in Column A (0, 1, 2, 3 .... 100+), and frequency in Column B. I'd like a simple way of calculating median age.

I thought I had the solution already. Add cumulative frequency in column C, starting with 0 in C2, entering =B2 into C3, =B3+C3 into C4, =B4+C4 into C5, and so on. Enter the position of the median into cell E3 by using =(C103 + 1) / 2 (where C103 equals the total cumulative frequency). Then, finding the median itself by using =LOOKUP(E3, C2:C103, A2:A102).

Whilst that solution seems appropriate in the vast majority of cases, it occurred to me that a particular median position might fall between two categories. For example, we might get the median position of 100.5, where person in Position 100 is aged 30 and person in Position 101 is aged 31. If we work through that manually we can calculate that the median age should be 30.5, but if I'm not mistaken the process I've described above would return the median age as 31 in that case. I'll need to repeat this for a large number of datasets and won't be able to check them all manually, so I think the formula itself needs to be changed, but this is where my Excel skills fall short.

Does anybody know how I can update the solution above to calculate the median correctly, including any cases in which the median position falls between two categories?

I’m helping a friend with a work project and a bit stumped. She’s trying to find the median wait time for ~2500 individuals. Basically it’s formatted as:

A1) 01:30 A2) 29:30 A3) 15:45 A4) 120:20 … A2500) 30:20

Usually, I’d put “=MEDIAN(A1:A2500)”, but it’s coming back with the #NUM! error. I know the median has a limit of usually 255 data points and I’d blame that, but she managed to get the median for January’s 2500 data points with no issue and is running into this issue on every other month. SOS?