Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Needed help on Median calculation (Microsoft Excel 2003)

    I have a sample sheet of Employee data and I want to create a summary sheet of Experience classification and for each classification, I want the Median, min and max salary calculated automatically from the Details sheet. Can I do this using a macro/formula? Please help me. I have been unable to use statistical formulas (except DAVERAGE, etc) in this case.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Needed help on Median calculation (Microsoft Excel 2003)

    Welcome to Woody's Lounge!

    You can use an array formula for this. After entering a formula, confirm it with Ctrl+Shift+Enter instead of just Enter to turn it into an array formula.

    The formula for Median Salary in F5 could be
    <code>
    =MEDIAN(IF(Details!$E$2:$E$7=$D5,Details!$K$2:$K$7 ))
    </code>
    The disadvantage of this formula is that it will return #ERROR if there are no data for the experience range. To avoid this, you can expand the formula to
    <code>
    =IF($E5>0,MEDIAN(IF(Details!$E$2:$E$7=Summary!$D5, Details!$K$2:$K$7)),"")
    </code>
    This will return an empty string "" if there are no data. The formulas for MIN and MAX are similar. See the attached version.

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Needed help on Median calculation (Microsoft Excel 2003)

    This was incredible, Hans. You responded back with the solution within minutes of my posting the query. Truly appreciate your help!

    Thanks

    Pallav

Posting Permissions

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