# Thread: Help with Median, of multiple pabs, excluding zero's

1. ## Help with Median, of multiple pabs, excluding zero's

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!

I added an excel file including an example of my situation, the tap called median is where i want to see the median value. I inserted the formula i descripe above and it gives the #REF! error.

Best, Ward

2. 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.

3. Hi,
Good luck
Example for forum with OR.xlsx

4. I do not know how you got products, but here's guidelines as you try to solve a problem. Perhaps you help this idea