# Formula to return median values if true

#### sparky

##### New member
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.

#### Attachments

• Book2.xlsx
8.9 KB · Views: 10

#### NBVC

##### Super Moderator
Staff member
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
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
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?