Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jul 2001
    Location
    Edison, New Jersey, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MIN with 2 conditions (XL2000)

    TIA for your help.

    I want to execute the MIN function only for the records where 2 other conditions are met. Here is a statement that does not work but it is useful for explaining what I am trying to do:

    =MIN(IF('NJDBI_AR closed'!E2:E26="X")*('NJDBI_AR closed'!I2:I26="X"),'NJDBI_AR closed'!R2:R26)

    I am looking for the smallest value in Column R of those records that have an "x" in columns E and I.

    I will also then do a MAX for the same columns and assume the correct approach will work for both MIN & MAX - so please let me know if that is Not the case.

    Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MIN with 2 conditions (XL2000)

    You were almost there. The formulas should be array formulas, i.e. confirm with Ctrl+Shift+Enter instead of just Enter. And there is a closing parenthesis missing at the end. The MAX formula should work just the same.

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: MIN with 2 conditions (XL2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Rob

    Again my answer is quick and with limited testing:

    1) Use a new column, hidden if you want, and use the formula that would copy the value from the column you want to work with I think R into that hidden column if there is an x in the column that you are evaluating I think E and I something like <font color=blue>=IF(AND(E1="x",I1="x"),R1,"")</font color=blue>

    2) Use the Min and Max functions on that column.

    Hope this helps

    Wasim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Star Lounger
    Join Date
    Jul 2001
    Location
    Edison, New Jersey, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MIN with 2 conditions (XL2000)

    Thanks guys. Got it. This works:

    {=MAX(IF(('NJDBI_AR closed'!E2:E26="X")*('NJDBI_AR closed'!I2:I26="X"),'NJDBI_AR closed'!R2:R26))}

    I made a couple of mistakes... I correctly entered in my post something that was not correct in my spreadsheet! and my data was not clean in the sense that what I was darn sure was the correct MIN and MAX were NOT the correct MIN and MAX! Brain gas is a bear when you get old!

    Enjoy!

Posting Permissions

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