# Thread: Formula to return median values if true

1. ## Formula to return median values if true

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.

2. 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. 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. 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
•