# Thread: MIN with 2 conditions (XL2000)

1. ## MIN with 2 conditions (XL2000)

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.

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

4. ## 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
•