+ Reply to Thread
Results 1 to 4 of 4

Using median with multiple conditions

  1. #1
    Registered User
    Join Date
    03-08-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    33

    Using median with multiple conditions

    I was using a formula which returned the median value of a number of entries based on a specific risk and objective for a given PM code, which worked quite happily (B4 to B12).

    However if I want to do the same specific risk and objective but I want to include 2 PM codes the formula seems to fall apart as you can see in B3. The formula just ignores the PM part and calculate the median for ALL low-medium income entries rather than just those with TWW or JSW as the PM code.

    So in simple terms I would like to know what the formula is to calculate the median value of the entries for a low-medium income 12with JSW or TWW as their PM code ,the answer should be -3.69.

    Many thanks in advance.
    Attached Files Attached Files
    Last edited by jcswaby; 10-02-2020 at 06:35 PM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Using median with multiple conditions

    Please try

    =MEDIAN(IF((Sum1Yr[Obj]="Inc")*(Sum1Yr[Risk]="Low-Med")*((Sum1Yr[PM]="JSW")+(Sum1Yr[PM]="TWW")),Sum1Yr[Port TR]))

    Use + instead of OR and * instead of AND for array operation.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,983

    Re: Using median with multiple conditions

    D3=AGGREGATE(17,6,(Sum1Yr[Port TR])/(Sum1Yr[Obj]=F3)/(Sum1Yr[Risk]=G3)/(ISNUMBER(MATCH(Sum1Yr[PM],$H$3:$H$4,0))),2)
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-08-2015
    Location
    UK
    MS-Off Ver
    2013
    Posts
    33

    Re: Using median with multiple conditions

    Many thanks, those work nicely.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Median formula with multiple conditions
    By CHillFL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-22-2018, 03:55 PM
  2. Calculate Median based on multiple conditions
    By ncoday in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2018, 02:33 PM
  3. Calculate Median with multiple conditions
    By Masun in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-23-2017, 05:54 AM
  4. MEDIAN with multiple conditions
    By aarona in forum Excel General
    Replies: 3
    Last Post: 02-01-2017, 10:42 PM
  5. Median for Range: 2 Conditions
    By PraveshG81 in forum Excel General
    Replies: 1
    Last Post: 02-02-2013, 07:33 PM
  6. calculating MEDIAN with conditions, VBA
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-06-2012, 03:01 AM
  7. Median calculation on multi-range with conditions
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-22-2011, 03:32 PM

Tags for this Thread

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