1. Hi Excel Experts,

I need to arrive at the Min, Max, Average, Median salary based on 3 conditions. Though I can get the Min, Average, Max using a Pivot Table, i will not be able to get the Median.

Can someone help me with the formula. I have attached a sample sheet for your reference.

Col A has the data : Level, Sal, Code, Final Code

Col F:M is the format in which i need this formula. I want the Min, Median, Average, Max Salary based on the level, Code and final Code.

Thanks
Baiju

2. The following are all array formulas, confirmed with Ctrl+Shift+Enter.

J3: =MIN(IF((\$A\$3:\$A\$100=\$G3)*(\$D\$3:\$D\$100=\$H3),\$B\$3:\$ B\$100))
K3: =AVERAGE(IF((\$A\$3:\$A\$100=\$G3)*(\$D\$3:\$D\$100=\$H3),\$B \$3:\$B\$100))
L3: =MAX(IF((\$A\$3:\$A\$100=\$G3)*(\$D\$3:\$D\$100=\$H3),\$B\$3:\$ B\$100))
M3: =MEDIAN(IF((\$A\$3:\$A\$100=\$G3)*(\$D\$3:\$D\$100=\$H3),\$B\$ 3:\$B\$100))

Select J3:M3 and fill down.

3. Hi Hans,

Thanks for the formula. I used this on the sample template that i sent and it worked, however when i copied into my master data it doesnt seem to work. In my master date the data is on one sheet and the formula in the other, also the file is 1.5MB. is this the problem?.. really not able to figure it out.

Regards
Baiju

[quote name='HansV' post='780476' date='18-Jun-2009 12:13']The following are all array formulas, confirmed with Ctrl+Shift+Enter.

J3: =MIN(IF((\$A\$3:\$A\$100=\$G3)*(\$D\$3:\$D\$100=\$H3),\$B\$3:\$ B\$100))
K3: =AVERAGE(IF((\$A\$3:\$A\$100=\$G3)*(\$D\$3:\$D\$100=\$H3),\$B \$3:\$B\$100))
L3: =MAX(IF((\$A\$3:\$A\$100=\$G3)*(\$D\$3:\$D\$100=\$H3),\$B\$3:\$ B\$100))
M3: =MEDIAN(IF((\$A\$3:\$A\$100=\$G3)*(\$D\$3:\$D\$100=\$H3),\$B\$ 3:\$B\$100))

Select J3:M3 and fill down.[/quote]

4. If the formulas are on another sheet than the data, you must include the sheet name in the formula:

=MIN(IF(('Data Sheet'!\$A\$3:\$A\$100=\$G3)*('Data Sheet'!\$D\$3:\$D\$100=\$H3),'Data Sheet'!\$B\$3:\$B\$100))

You must, of course, adjust the various ranges and cell addresses as needed. And make sure that you confirm the formula with Ctrl+Shift+Enter.

5. Hi Hans,

I was able to figure it out.. thanks for the formula. Its worked for me now.

Regards
Baiju

[quote name='Baiju G Nath' post='780594' date='19-Jun-2009 03:45']Hi Hans,

Thanks for the formula. I used this on the sample template that i sent and it worked, however when i copied into my master data it doesnt seem to work. In my master date the data is on one sheet and the formula in the other, also the file is 1.5MB. is this the problem?.. really not able to figure it out.

Regards
Baiju[/quote]

#### Posting Permissions

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