MIN(IF( array formula with conditions

baitmaster

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,042
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...
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
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...
 
Last edited:
Upvote 0
baitmaster said:
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...
Click to expand...
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.
 
Upvote 0
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
 
Upvote 0
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...:rofl:
 
Upvote 0
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!
 
Upvote 0
baitmaster said:
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...:rofl:
Click to expand...
Looks OK to me! :biggrin:

Thanks for feeding back! :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Spilled array formula to match the minimum But above 0 of each row in a 2D table
Replies
2
Views
257
ED38
E
FrumpyJones
  • Solved
Trying to copy Conditional formatting across columns
Replies
9
Views
665
Fluff
Fluff
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
361
Trixterz
T
J
  • Solved
Spilled array formula to match column for minimum of each row in a 2D table
Replies
6
Views
1K
duggie33
D
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
465
GeneBF
G

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top