How to use median on groups of data

Hi all
My problem is I want to use the median function on groups of data in the one array.

Attached is a spreadsheet showing various stockcodes in various districts with their prices. My aim is to find the median stock price of each stockcode which might show me the district which has a huge variance in price with the same stock. Average can be used on a subtotal, but median is not there. The spreadsheet is an example and the real list is thousands of rows, so I can't manually go and change the range for each stockcode encountered to find the median for each stockcode.

Any suggestions, I am using excel 2003 and can't download add-ins due to my work's network security, though I could do it at home if someone found a good add-in etc.

Thanks
Robbo

Hi Max
Thanks for that, but to change the range manually for each different stockcode, if there were 60,000 rows would be impossible. I guess I need something maybe in VBA which can keep adjusting the range in your formula, or even the basic =median(range), and the range keeps adjusting when it picks up when a new stockcode is encountered and when finished.

Stockcode Median
1223 1.25 =MEDIAN(IF((A$2:A$5=A2)*(B$2:B$5=B2),C$2:C$5))
1560 2,180 =MEDIAN(IF((A$6:A$10=A2)*(B$6:B$10=B2),C$6:C$10))

Can any one help me with this next step?
Thanks
Anthony

Originally Posted by Max, Singapore

Try, array-entered* in D2:
=MEDIAN(IF((A$2:A$100=A2)*(B$2:B$100=B2),C$2:C$100))
Copy D2 down as far as required. Adapt the ranges to suit.

*press CTRL+SHIFT+ENTER to confirm the formula

mmm when I pasted in your formula in that example spreadsheet, I just got 27.5 all the way down the median column!
Are you able to attach my spreadsheet with the way you entered inthe formula coz I am not quite understanding it?

Originally Posted by Max, Singapore

But you don't have to change the range. Just adapt the example range within the array formula provided earlier to suit the full extent of your actual data. Then array-enter it and copy down. It should return the results that you seek.

If the actual range is quite large, to manage recalc performance, you might want to switch the book's calc mode to manual first before copying down, via Tools > Options > Calculation tab. Then after copying down, press F9 to recalc (this might take awhile to complete).

Sounds like you aren't "array-entering" the formula.

If you have the formula in D2 then select D2, press F2 then hold down CTRL and SHIFT keys and press ENTER. Curly braces should appear around the formula in the formula bar. Now copy the formula down the column

I just tried that, but the median price value for each row equalled the actual price of the stockcode.

I need the median price value for each group of identical stockcodes, so the in the example I initially posted, stockcode 1123 median price would be $1.25, stockcode 1560 median price would be $2,180 etc

Originally Posted by daddylonglegs

Sounds like you aren't "array-entering" the formula.

If you have the formula in D2 then select D2, press F2 then hold down CTRL and SHIFT keys and press ENTER. Curly braces should appear around the formula in the formula bar. Now copy the formula down the column

OK,

Max's suggested formula would give you the median for each unique stockcode/district combination. If you just want the median for each stockcode use this formula in D2 copied down

=MEDIAN(IF(A$2:A$100=A2,C$2:C$100))

confirmed with CTRL+SHIFT+ENTER

or perhaps to only show median for the first row of each stockcode

=IF(A2<>A1,MEDIAN(IF(A2:A$100=A2,C2:C$100)),"")

also confirmed with CTRL+SHIFT+ENTER

THANKS EVERYONE! I got it, I had to delete the bit in the formula that was looking at the districts because it was trying to do a median calc on unique combinations of stockcode and district, but I just needed stockcodes.

This is a great forum!

Originally Posted by robbo11

I just tried that, but the median price value for each row equalled the actual price of the stockcode.

I need the median price value for each group of identical stockcodes, so the in the example I initially posted, stockcode 1123 median price would be $1.25, stockcode 1560 median price would be $2,180 etc