+ Reply to Thread
Results 1 to 9 of 9

How to use median on groups of data

  1. #1
    Registered User
    Join Date
    10-29-2007
    Location
    melbourne
    Posts
    5

    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
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520
    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
    Max
    Singapore

  3. #3
    Registered User
    Join Date
    10-29-2007
    Location
    melbourne
    Posts
    5

    automatic range changing for median?

    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




    Quote 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

  4. #4
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520
    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).

  5. #5
    Registered User
    Join Date
    10-29-2007
    Location
    melbourne
    Posts
    5
    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?


    Quote 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).

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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

  7. #7
    Registered User
    Join Date
    10-29-2007
    Location
    melbourne
    Posts
    5

    median frustrations

    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

    Quote 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

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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

  9. #9
    Registered User
    Join Date
    10-29-2007
    Location
    melbourne
    Posts
    5

    median problem solved

    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!


    Quote 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1