Results 1 to 5 of 5
Thread: Median, Min, Max

20090618, 07:36 #1
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.
Can someone please help with with a simple but great formula.
Thanks
Baiju

20090618, 08:13 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20090618, 23:45 #3
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
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='18Jun2009 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]

20090619, 03:12 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20090619, 04:23 #5
 Join Date
 Jul 2006
 Location
 Bangalore, India
 Posts
 180
 Thanks
 1
 Thanked 0 Times in 0 Posts
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='19Jun2009 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]