Error with Median and IF Function

[AmyYang]

Hi, 

I am trying to use Median and IF excel function and unfortunately there is a problem and I do not understand which part of formula I wrote incorrectly.

Could someone kindly help review my formula and let me know which part I made mistake? 

Thanks so much,

Amy 

[#2]

@AmyYang 

=MEDIAN(IF((Table1[2]=[@Country])*(Table1[3]="Interested"),Table1[5],""))

You can try this formula. In order to remove error messages you can as well wrap the formula into an IFERROR function.

=IFERROR(MEDIAN(IF((Table1[2]=[@Country])*(Table1[3]="Interested"),Table1[5],"")),"")

[#3]

@OliverScheurich Hi Quadruple Pawn, thanks very much! I forgot to mention that the outputs for Australia says "0.00" but it is incorrect because it should say "0.33" for Australia as per the data, and I do not understand why our formula isn't picking it up. I'm wondering if you know why? 

[#4 · Accepted solution]

@AmyYang 

For "Autralia" in column B there are rows 9, 10 and 11 which say "Interested" in column C. The corresponding entries in column E are " 0,33 ",  " blank " and " blank " and the median of these values is " 0,00 ". The " blank " are recognized as " 0 " as you can see below.

With this formula the blank values in column E are excluded and the intended result is returned. In my first reply i didn't understand that the blanks (zero values) have to be excluded.

=MEDIAN(IF((Table1[2]=[@Country])*(Table1[3]="Interested")*(Table1[5]<>""),Table1[5],""))

[#5]

@OliverScheurich 

Wow, thank you so much for your review again and explanation!

That is really helpful to know. I used to think that only if I wrote "0" then Excel will consider that.

I did not know that Excel also considers blank as equal to "0"! That is really good to know and I will ensure to use your updated formula that will exclude the blanks. Thank you! :)