+ Reply to Thread
Results 1 to 5 of 5

conditioned Median

  1. 02-21-2017, 06:33 AM #1
    MikaelChristiansen
    MikaelChristiansen is offline
    Registered User
    Join Date
    02-21-2017
    Location
    Aarhus, Denmark
    MS-Off Ver
    2010
    Posts
    3

    conditioned Median

    I have a large dataset for medical research. 230 patients belonging to either of 6 diagnostic groups: RRMS, PPMS, SPMS, CIS, SC and OND

    I need medians for the test result for each diagnostic group.

    Column A Column B
    Diagnosic group result of test
    RRMS 110
    PPMS 200
    RRMS 80
    SC 5
    CIS 25
    RRMS 95
    PPMS 150
    CIS 50
    RRMS 150
    RRMS 87


    I need a formula that will return Median of result of test values for each diagnostic group.

    I already tried : Median(IF(A:A="RRMS", B:B)) - but doesnt work..

    Can anyone help? Regards Mikael
    Reply With QuoteRegister To Reply
  2. 02-21-2017, 07:11 AM #2
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: conditioned Median

    there are strings not numbers in your data
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 02-23-2017, 06:26 AM #3
    MikaelChristiansen
    MikaelChristiansen is offline
    Registered User
    Join Date
    02-21-2017
    Location
    Aarhus, Denmark
    MS-Off Ver
    2010
    Posts
    3

    Re: conditioned Median

    Hi Tim

    Yes, I have strings (diagnoses) in column A and numbers (result of test) in column B
    Reply With QuoteRegister To Reply
  4. 02-23-2017, 06:39 AM #4
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,137

    Re: conditioned Median

    With your categories listed in D2 to D7, use this array formula in E2, copied down:

    =IFERROR(MEDIAN(IF($A$2:$A$11=D2,$B$2:$B$11)),"")

    Given your location, you may need ; instead of ,

    This is an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  5. 02-23-2017, 07:47 AM #5
    MikaelChristiansen
    MikaelChristiansen is offline
    Registered User
    Join Date
    02-21-2017
    Location
    Aarhus, Denmark
    MS-Off Ver
    2010
    Posts
    3

    Re: conditioned Median

    Thx Glenn, very helpfull, works perfectly!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. If statement Median calculation returning #N/A instead of median
    By carrod65 in forum Excel General
    Replies: 8
    Last Post: 08-09-2016, 12:50 AM
  2. [SOLVED] Using Median IF to calculate the median for a specific criteria
    By boynejs in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-20-2014, 01:50 AM
  3. Using Median to get the median of seconds per day per checkpoint
    By dodgerpixie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2014, 03:38 AM
  4. Median Indirect: Find median in range and bring back adjacent cells
    By Keelin in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-27-2014, 08:31 AM
  5. [SOLVED] Median Ifs, need to find median $ amount per deal for each year
    By xenomorph8472 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2012, 02:01 PM
  6. value according last conditioned value
    By MartyZ in forum Excel General
    Replies: 1
    Last Post: 03-12-2012, 04:17 AM
  7. use if conditioned to several conditions
    By silviario in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-04-2011, 03:34 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1