Forum Discussion
Solved
Error with Median and IF Function
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 m...
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],""))
=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],"")),"")