# Thread: Min, Median & Max (Excel 2003)

1. ## Min, Median & Max (Excel 2003)

Hi Excel Experts,

I have an excel sheet which has the departments in Column C, Grades in Column C and Salary in Column B.

I require the Min, Median & Max Salaries Departmentwise and gradewise.

i.e. under Department A I have various grades for each grades I need the Min, Median & Max.

I can get the Min & Max using Pivot Table. I also need the Median, can someone help me with a formula for the same.

Regards
Baiju

2. ## Re: Min, Median & Max (Excel 2003)

Once you have the pivot table, you can add a column next to it to calculate the median. The attached version of your workbook uses array formulas with MEDIAN and IF, for example:

=MEDIAN(IF(\$D\$2:\$D\$101=G3,\$B\$2:\$B\$101))

where G3 contains a department name. (Array formulas must be confirmed with Ctrl+Shift+Enter)

3. ## Re: Min, Median & Max (Excel 2003)

Hi Hans,

Thanks for your formula, it is fine when i have to get the median department wise, however the same will not apply when i have to get the median Gradewise under each department.

I have redone the Pivot Table to give you a better understanding on what i am looking at.

Regards
Baiju

4. ## Re: Min, Median & Max (Excel 2003)

You have to make sure that you refer to the correct department, and you have to add the grade to the condition. See attached version.

5. ## Re: Min, Median & Max (Excel 2003)

Hi Hans,

I know its a delayed response to your formula.. thanks Hans, the formula worked perfectly... got the intended result.

Regards
Baiju

6. ## Re: Min, Median & Max (Excel 2003)

Hi Hans,

I wanted to download the attachement, but unable to do so. Is there a way to get it .. i now need that formula. ;-).

Regards
Baiju

7. ## Re: Min, Median & Max (Excel 2003)

Many attachments were lost when the Lounge server crashed last month. Can you attach your workbook again?

8. ## Re: Min, Median & Max (Excel 2003)

Hi Hans,

My posting If AND Condition has the attachment.

Regards
Baiju

9. ## Re: Min, Median & Max (Excel 2003)

a) How could I have known that?
[img]/forums/images/smilies/cool.gif[/img] That is a different workbook, it doesn't contain salaries.

#### Posting Permissions

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