Find medians from a frequency distribution table?

[JustSteve88]

Hi all, new to this site. I'm hoping to find an answer to this. I have been looking to figure this out for some time, and have tried a couple of methods that don't work quite right.
I want to find the median in a frequency distribution table in excel. I only have one sample group to work from.
I can't simply use the =MEDIAN formula because it only returns a median for the given data points (the classes), but does not factor in the frequencies.
I have referenced this old thread here: Find medians from a frequency distribution table?
I think that the formulae used in this thread could work, but I'm having trouble translating it to use with the data set in columns instead of rows.

Here is my Data:

Values: Frequency:
1 19
2 11
3 10
4 6
5 2
6 2

Can anyone help me either reformat one of the previously given formulae for this, or help with a different formula? It would be much appreciated. Thanks!

PS- an added plus would be if you could also help me with the mode, but I might be able to sort that on my own.

 

[JustSteve88]

Sorry, the above post was supposed to have the data arranged in columns. Here's a better rendition (maybe):
V--F
1--19
2--11
3--10
4--6
5--2
6--2

 

[Fluff]

Hi & welcome to MrExcel.
Based on Barry's formula from the other thread, how about

+Fluff 1.xlsm
	A	B	C	D
7	1	19		2
8	2	11
9	3	10
10	4	6
11	5	2
12	6	2
Main

Cell Formulas
Range	Formula
D7	D7	=MEDIAN(LOOKUP(SEQUENCE(SUM(B7:B12),,0),SUBTOTAL(9,OFFSET(B6,0,0,SEQUENCE(ROWS(A7:A12)))),A7:A12))

 

[JustSteve88]

Hi & welcome to MrExcel.
Based on Barry's formula from the other thread, how about

+Fluff 1.xlsm
	A	B	C	D
7	1	19		2
8	2	11
9	3	10
10	4	6
11	5	2
12	6	2
Main

Cell Formulas
Range	Formula
D7	D7	=MEDIAN(LOOKUP(SEQUENCE(SUM(B7:B12),,0),SUBTOTAL(9,OFFSET(B6,0,0,SEQUENCE(ROWS(A7:A12)))),A7:A12))

Oh Great! this works. I'm wodering if it's possible that you could break this formula down for me? I don't fully understand how all of these pieces work.

 

[Fluff]

This part SEQUENCE(SUM(B7:B12),,0)produces an array of number from 0 to 1 less than the sum of B7:B12 giving
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49}

This SUBTOTAL(9,OFFSET(B6,0,0,SEQUENCE(ROWS(A7:A12)))) creates a running sum of the numbers in B7:B12 starting with 0 giving
{0;19;30;40;46;48}

The lookup the looks for the values in the 1st array in the 2nd array & if not found return the nearest lower number & then returns the corresponding values from A7:A12 giving
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;2;2;2;2;2;2;2;2;2;2;2;3;3;3;3;3;3;3;3;3;3;4;4;4;4;4;4;5;5;6;6}

HTH

 

[JustSteve88]

That's fantastic! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[trollsy____]

Hi & welcome to MrExcel.
Based on Barry's formula from the other thread, how about

+Fluff 1.xlsm
	A	B	C	D
7	1	19		2
8	2	11
9	3	10
10	4	6
11	5	2
12	6	2
Main

Cell Formulas
Range	Formula
D7	D7	=MEDIAN(LOOKUP(SEQUENCE(SUM(B7:B12),,0),SUBTOTAL(9,OFFSET(B6,0,0,SEQUENCE(ROWS(A7:A12)))),A7:A12))

Hi Fluff,

Is there a way to transform the formula for a much larger range than this, at all?

I have a huge tail to my distribution and wondered if there was a way to amend the formula to fit my data set (see attached image).

Thank you in advance

 

[Fluff]

Hi & welcome to MrExcel.
You can simply change the range the formula is looking at. If you need further help, then please start a new thread for your question.
Thanks