Formula to return median values if true

sparky

New member
Joined
Dec 17, 2013
Messages
4
Reaction score
0
Points
0
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.
 

Attachments

  • Book2.xlsx
    8.9 KB · Views: 10

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

sparky

New member
Joined
Dec 17, 2013
Messages
4
Reaction score
0
Points
0
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?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Not sure I follow. You say F2:F10 all have numbers, but you are still getting #N/A? Is the D2:D10 matching the P1? Is the formatting of the numbers numbers or text? Can you attach a sample with expected results?
 
Top