Median of multiple pabs, excluding zero's

[WardM]

Dear All,

Using office 2010, i ran into a problem when i wanted to calculate the median of one cell (C13) on different tabs but exclude all the zero's.

I first tried the formula "{=Median(IF(Sheet1!C13;Sheet2!C13;...Sheet28!C<>0;Sheet1!C13;Sheet2!C13;...Sheet28!C))}"
but got the error "to many arguments for this function" i assume this is due to the ";" separating the arguments in the IF(...) part of the formula

I then tried the formula "{=Median(IF(Sheet1:Sheet28!C13<>0;Sheet1:Sheet28!C13))}" but got the error #REF!. I assume this is due to the fact that "Sheet1:Sheet24!C13" is not a valid reference. I tried to find an alternative but did not succeed and i am afraid this is due to the fact that the IF() formula may not handle 3-D references.

I am not acquainted with User Defined Functions (UDF) but i think it is the only solution?

Could you help me with finding a solution? Any help and advice is appreciated!

Best, Ward

 

[WardM]

A third attempt was with this formula
"{=MEDIAN(IF({"AT";"BE";"BG";"HR";"CY";"CZ";"DK";"EE";"FI";"FR";"DE";"GR";"HU";"IE";"IT";"LV";"LT";"LU";"MT";"NL";"PL";"PT";"RO";"SK";"SI";"ES";"SE";"UK"}&"!D13">0;{"AT";"BE";"BG";"HR";"CY";"CZ";"DK";"EE";"FI";"FR";"DE";"GR";"HU";"IE";"IT";"LV";"LT";"LU";"MT";"NL";"PL";"PT";"RO";"SK";"SI";"ES";"SE";"UK"}&"!D13"))}"
But that failed as wel as i got the #NUM! error message, and i think the {"AT";"BE";"BG";"HR";"CY";"CZ";"DK";"EE";"FI";"FR";"DE";"GR";"HU";"IE";"IT";"LV";"LT";"LU";"MT";"NL";"PL";"PT";"RO";"SK";"SI";"ES";"SE";"UK"}&"!D13" reference is not recognized as valid.

 

[XOR LX]

Hi.

Assuming the values in those cells are always non-negative:

=PERCENTILE.INC(Sheet1:Sheet7!C13,1/2+INDEX(FREQUENCY(Sheet1:Sheet7!C13,0),1)/(2*(SUM(FREQUENCY(Sheet1:Sheet7!C13,0))-1)))

Replace Sheet1:Sheet7 appropriately.

Actually, if the number of sheets in question is fixed, the construction used for the denominator can be replaced with a static value n:

=PERCENTILE.INC(Sheet1:Sheet7!C13,1/2+INDEX(FREQUENCY(Sheet1:Sheet7!C13,0),1)/n

where:

n = 2 x (Number of Sheets - 1)

(Which in my example would evaluate to 12.)

Regards

 

[WardM]

Hi XOR LX,

It worked, thank you! I don't know if i would have been able to figure this out myself even if i would have spend ages on the problem, thank you very much!

Best regards,

Ward

 

[XOR LX]

You're welcome!

Cheers!

 

[Aladin Akyurek]

Using a rather classical approach, control+shift+enter, not just enter:

=MEDIAN(IF(ISNUMBER(1/N(INDIRECT("'"&SheetList&"'!C13"))),N(INDIRECT("'"&SheetList&"'!C13"))))

where SheetList refers to a range that lists all of the relevant data sheets one by one.