Median formula to ignore blank cells

[Hanz77]

I'm trying to find the median on a range of numbers; however, the data is downloaded from another source and has the occasional blank cell. The Median formula automatically counts these blanks as zeros and skews my results. Following is my current formula and an example of the data I am using.

{=MEDIAN(IF(A1:A2000=15,B1:B2000))}

Column A contains random numbers ranging from 1 to 20. I am trying to get the median of all numbers in column B that has the number 15 in Column A. As originally stated, there are instances where there could be the number 15 in column A; however, the coresponding number in column B could be blank. I want this blank ignored and not counted as zero for my median amount. Also, I cannot delete the rows containing these blanks as there is additional data that I need in column C through M.

Any suggestions are appreciated.

 

[Scott Huish]

How about:

=MEDIAN(IF(A1:A2000=15,IF(B1:B2000<>"",B1:B2000)))

Confirm with CTRL-SHIFT-ENTER

 

[Gerald Higgins]

Quick and dirty - sort your data on col B to bring all the blanks together, then modify your formula to exclude the rows containing blanks

 

[Hanz77]

Thanks HOTPEPPER, your formula works great.