Results 1 to 4 of 4

Thread: Formula to return median values if true

  1. #1

    Formula to return median values if true



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I'm stuck with this issue for a while now.

    I have attached the spreadsheet. This is basically what I want to achieve but excel will not allow me to put this formula in,

    if($D:$D = "P1", median($F:$F), "0")

    I want the formula to check the column D for rows with P1 and if true, return the median of the corresponding rows in column F.

    Can some one please help me out?

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =MEDIAN(IF($D$2:$D$10="P1",$F$2:$F$10))

    confirmed with CTRL+SHIFT+ENTER not just ENTER.

    Don't use whole column ranges with this formula.


  3. #3
    Thanks NBVC for your prompt reply. The formula is working fine in the attached spreadsheet. But I'm trying to copy it over to a number of other spreadsheets and I'm getting a #N/A error. Can you please tell me how to resolve a #N/A error for the median formula?

    Upon evaluating the formula, I figured that the If statement works fine, but when the median formula tries to search the array ($F$2:$F$10), this returns #N/A. All the ranges I'm searching have values in them, there are no blank cells. (but this formula must treat blank cells as 0)

    How can I achieve this?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Not sure I follow. You say F2:F10 all have numbers, but you are still getting #N/A? Is the D210 matching the P1? Is the formatting of the numbers numbers or text? Can you attach a sample with expected results?


Posting Permissions

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