LinkBack URL
About LinkBacks
I am trying to run the function =MEDIAN(IF(AJ$2:AJ$104=1,$AB$2:$AB$104)) while some of the cells contain #VALUE!.
I want to ignore these #VALUE! not even regarding these as zeros which is not true.
Is there a way to do this work?
I am trying to run the function =MEDIAN(IF(AJ$2:AJ$104=1,$AB$2:$AB$104)) while some of the cells contain #VALUE!.
I want to ignore these #VALUE! not even regarding these as zeros which is not true.
Is there a way to do this work?
Try...
=MEDIAN(IF((AJ$2:AJ$104=1)*NOT(ISERROR($AB$2:$AB$104)),$AB$2:$AB$104))
But it is much better to clean up range where error is found using =IFERROR('Your formula',"") or other variants.
SimilarlyFormula:
Please Login or Register to view this content.
Dave
If you use AGGREGATE function that has a built-in option to ignore error values, i.e.
=AGGREGATE(16,6,$AB$2:$AB$104/(AJ$2:AJ$104=1),0.5)
[16 = PERCENTILE function but PERCENTILE set to 0.5 is the same as MEDIAN]
Audere est facere
Another option might be to remove the #VALUE error by adjusting the formula that is causing it?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks