Median IF returns #NUM! when criteria are not met instead of blank or 0

[ech1982]

I am trying to calculate a median based on several criteria. The formula works fine when the criteria are all met, but when they are not I get #NUM !. I would rather it returned a blank or a 0.




Here's the formula I'm using:
{=MEDIAN(IF(Results[Rider]=[@Rider],IF(Results[Event_Type]="CDM",Results[Rank],"")))}


In some cases there is no "CDM" result and that's where I get the error. It doesn't seem to make a difference whether I include ,"" for a false.
Any help, greatly appreciated.

 

[Eric W]

Try putting IFERROR around it:

=IFERROR(MEDIAN(IF(Results[Rider]=[@Rider],IF(Results[Event_Type]="CDM",Results[Rank],""))),"")

 

[ech1982]

Try putting IFERROR around it:

=IFERROR(MEDIAN(IF(Results[Rider]=[@Rider],IF(Results[Event_Type]="CDM",Results[Rank],""))),"")

That worked perfectly. Thanks so much Eric W
SOLVED!

 

[ech1982]

That worked perfectly. Thanks so much Eric W
SOLVED!

As a follow up question, I would like to find the median within the same range but where two criteria are both met within the same array.

Original formula:
{=IFERROR(ROUNDUP(MEDIAN(IF(Results[Rider]=[@Rider],IF(Results[Event_Type]="CDM",Results[Rank],""))),0),"0")}

Formula with additional Event Type of 'CM':
=IFERROR(ROUNDUP(MEDIAN(IF(Results[Rider]=[@Rider],IF(Results[Event_Type]="CDM",Results[Rank],IF(Results[Event_Type]="CM",Results[Rank],""))),0),"0")

I would like to find the median from the combined results from CDM and CM (in some cases there will be one but not the other and in some case no result at all) but this formula as well using an IF(AND or and IF(OR seem to produce the incorrect result.
Thanks