Results 1 to 4 of 4

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

  1. #1

    Help with Median, of multiple pabs, excluding zero's



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #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. #3
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    102
    Articles
    0
    Excel Version
    365
    Hi,
    I hope this file will help you.
    Good luck
    Example for forum with OR.xlsx

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    I do not know how you got products, but here's guidelines as you try to solve a problem. Perhaps you help this idea
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •