Find Median value when totals are summed and not in a range

**Winwell** · 03-17-2015, 09:32 AM

Hello - this has been bugging me.

I want to find the median value (in this case it is an exam grade but that's just for an example) where I only have the total number of students that achieved each grade. So I know there are 100 Grade A, 200 Grade B, 300 Grade D and so on. I might end up with a sample of thousands of students. It would be unfeasible to list individual scores in a range in order to determine the median grade that way so is there a way to do it using a formula when all I know is the totals for each grade? I also want to plot the summary on a bar chart and show the median grade.

i attach an example sheet if that's any use.

Any help solving this would be great. Thanks

**XOR LX** · 03-17-2015, 09:50 AM

Hi.

Maybe:

=INDEX(A11:A15,MATCH(TRUE,MMULT(N(ROW(INDEX(A:A,1):INDEX(A:A,COUNTA(A11:A15)))>=COLUMN(INDEX(1:1,1):INDEX(1:1,COUNTA(A11:A15)))),B11:B15)>=SUM(B11:B15)/2,0))

though you haven't clarified what the result should be were you to have an odd number of values in your list and the median fell between two subtotals.

Regards

**Kaper** · 03-17-2015, 09:55 AM

To find median in non-numerical range like grades:
- in C11 write 0
- in C12:

Formula:

Please Login or Register  to view this content.

and copy down to one row below data (C16)
- in B18:

Formula:

Please Login or Register  to view this content.

so you have formula returninhg your C

**Kaper** · 03-17-2015, 10:03 AM

To display this value on the graph in some empty column (say in cell N11) I'd write:

Formula:

Please Login or Register  to view this content.

copy this doen and use as a source for second series. Then I'd add error bars (100%, both negative and positive) and format them to mimmic the line
Or may be no /2 in the formula and only negative error bar would look better?

**Winwell** · 03-17-2015, 11:57 AM

Thaniks both for your help with this.

**daddylonglegs** · 03-17-2015, 06:29 PM

given that the grades are listed in order you could try this formula to give the median grade

=LOOKUP(SUM(B11:B15)/2,SUBTOTAL(9,OFFSET(B10,0,0,ROW(B11:B15)-ROW(B11)+1)),A11:A15)

If the median value is halfway between 2 consecutive grades this will give you the lower of the two

**Winwell** · 03-18-2015, 04:03 AM

Originally Posted by daddylonglegs

given that the grades are listed in order you could try this formula to give the median grade

=LOOKUP(SUM(B11:B15)/2,SUBTOTAL(9,OFFSET(B10,0,0,ROW(B11:B15)-ROW(B11)+1)),A11:A15)

If the median value is halfway between 2 consecutive grades this will give you the lower of the two

Thanks for the help

Find Median value when totals are summed and not in a range

LinkBack

Thread Tools

Rate This Thread

Display

Find Median value when totals are summed and not in a range

Re: Find Median value when totals are summed and not in a range

Re: Find Median value when totals are summed and not in a range

Re: Find Median value when totals are summed and not in a range

Re: Find Median value when totals are summed and not in a range

Re: Find Median value when totals are summed and not in a range

Re: Find Median value when totals are summed and not in a range

Thread Information

Users Browsing this Thread

Similar Threads

Median Indirect: Find median in range and bring back adjacent cells

How do find Median of a set of totals and find the relating grade

Pivot Table Totals Incorrectly Summed

[SOLVED] Find Median of Positive numbers only in Range

[SOLVED] Find Median of Positive numbers only in Range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions