Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    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.

  3. #3
    2 Star Lounger
    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='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. #4
    Plutonium Lounger
    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.

  5. #5
    2 Star Lounger
    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='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
  •