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

Here is a method using some helper cells, without VBA:

On another sheet, enter the sheet names in A1:A28.

If the sheet names are really Sheet1 ... Sheet28, you can enter Sheet1 in A1, then fill down to A28.

Enter the following formula in B1:

=INDIRECT("'" & A1 & "'!C13")

and fill down to B28.

The following array formula, confirmed with Ctrl+Shift+Enter, will calculate the median:

=MEDIAN(IF(B1:B28<>0,B1:B28))

Warning: if the sheet names are changed, you'll have to edit the list of names in A1:A28 too.

It worked! thank you very much!