MIN(IF( array formula with conditions

[baitmaster]

Hey all, I've been helping Howerd with some formulas, but he's got me stumped with one. I'm reposting this question in a new thread, as I think not many people will be looking at the original thread, under the misguided belief that I have it covered

Original post http://www.mrexcel.com/forum/showthread.php?t=575396&page=2

Summary:
We can use MIN(range) to find the minimum value
We can use MIN(IF(range1 = condition,range2) entered as array, to return minimum value where condition is met
What formula would work on the condition if it exists, or the full range if it doesn't?

I think its like MIN(IF(OR(range1 = criteria,criteria = ""),range2)), but I'm always returning the full range2 in all circumstances

Note, this needs to be scaleable to multiple ranges of conditions. I can't think how to ignore the test if it's null...

 

[sous2817]

Something like this seems to work for me:

=MIN(IF(A1:A9="A",B1:B9,MIN(B1:B9)))

wait...no it doesn't. Let me quit eating and try again...

 

[T. Valko]

Hey all, I've been helping Howerd with some formulas, but he's got me stumped with one. I'm reposting this question in a new thread, as I think not many people will be looking at the original thread, under the misguided belief that I have it covered

Original post http://www.mrexcel.com/forum/showthread.php?t=575396&page=2

Summary:
We can use MIN(range) to find the minimum value
We can use MIN(IF(range1 = condition,range2) entered as array, to return minimum value where condition is met
What formula would work on the condition if it exists, or the full range if it doesn't?

I think its like MIN(IF(OR(range1 = criteria,criteria = ""),range2)), but I'm always returning the full range2 in all circumstances

Note, this needs to be scaleable to multiple ranges of conditions. I can't think how to ignore the test if it's null...

Test to see if the condition exists.

If it does then MIN(IF... else MIN(entire range)

Something like this, array entered**:

=IF(COUNTIF(A1:A10,"x"),MIN(IF(A1:A10="x",B1:B10)),MIN(B1:B10))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[baitmaster]

Thanks chaps, although I am struggling to extend this to multiple conditions. I think maybe the Dfunctions DMIN and DMAX are the way forward instead...

Am gonna think about it overnight, and see how I feel in the morning

 

[baitmaster]

OK, I used a load of named ranges to simplify the output. A formula for finding the minimum value in a dataset, subject to two criteria being met if used or ignored if not used, is as follows:

=IF(condition1<>"none",IF(condition2<>"none",MIN(IF(range1=condition1,IF(range2=condition2,values))),MIN(IF(range1=condition1,values))),IF(condition2<>"none",MIN(IF(range2=condition2,values)),MIN(values)))

entered as array formula using shift + ctrl + enter

It ain't pretty, and is probably better replaced with DMIN, but as no-one uses DMIN (?), and a complex array formula always impresses colleagues that little bit more, I'd use this

Please don't ask me to extend it to 3 conditions...

 

[howerd]

Thanks Baitmaster,

That works a treat......I did need a 3rd condition on one of my tables . However, I have added a couple of columns to the Data tab to take care of the 3rd condition, which has allowed me to incorporate your formula.

Thanks again for all your help!

 

[T. Valko]

OK, I used a load of named ranges to simplify the output. A formula for finding the minimum value in a dataset, subject to two criteria being met if used or ignored if not used, is as follows:

=IF(condition1<>"none",IF(condition2<>"none",MIN(IF(range1=condition1,IF(range2=condition2,values))),MIN(IF(range1=condition1,values))),IF(condition2<>"none",MIN(IF(range2=condition2,values)),MIN(values)))

entered as array formula using shift + ctrl + enter

It ain't pretty, and is probably better replaced with DMIN, but as no-one uses DMIN (?), and a complex array formula always impresses colleagues that little bit more, I'd use this

Please don't ask me to extend it to 3 conditions...

Looks OK to me!

Thanks for feeding back!