Median

in column a1 to a5000 i have dates , multiple entries for different dates, in column b1 to b5000 i have values for each row, i need in column c to take the median of all the values in b that share the sme date in column a and to display this median on all rows in column c as i need to use these figures to do a calculation for each row individually, i am relatively new to excel 2010 but can find the average but need the median, i am stumped, please can somebody help it if it is possible

Originally Posted by 6StringJazzer

Welcome to the forum!

In your sample data the data is sorted ascending by date. Will this always be true? If so then this formula will work in C2, copy down

Please Login or Register  to view this content.

thanks will have a go at that , the data will not always be in ascending order they will be sorted often and could be jumbled up is there a solution if this is the case, my spreadsheet is massive with many calculations , the example i posted is just a mock up to see if it was possible

Originally Posted by ChemistB

My formula will not require sorting but depending on the complexity of your spreadsheet, might slow down the calculations. (Array formulas have a tendancy to do that)

thanks ChemistB, that looks excellent will try it on my big speadsheet but works really well on my example, excellent swift help on this forum, been googling like madman and couldn't find an answer , looks like i might have it now thanks to you guys, cheers

Originally Posted by ChemistB

Try this in C2 dragged down. It needs to be entered as an Array (CNTRL SHFT ENTER rather than a simple ENTER). If entered properly, you'll see {} around your formula

=MEDIAN(IF($A$2:$A$76=A2,$B$2:$B$76,""))

Does that work for you?

just checked this on my big spreadsheet and it appears you have solved the problem for me , many thanks