# Thread: Needed help on Median calculation (Microsoft Excel 2003)

1. ## 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. ## 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. ## 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
•