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