Smallest value (earliest date) but not in a list. How to adapt MIN(IF(...) function

Uganda

Board Regular
Joined
Jun 10, 2015
Messages
72
Hello

I need to find the earliest date out of four fields but they are not next to each other.

I have seen the below formula:

=MIN(IF(A1:A4>=01/07/15, A1:A4))

However I can't seem to make it work when not selecting a list to choose from i.e. I want to choose from WT2, ABO2, ABR2 and ACP2.

Can anyone advise what formula I need to use?

Thanks so much!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,306
Office Version
  1. 365
Platform
  1. Windows
They do not need to all be next to each other.
For example,
Code:
=MIN([COLOR=#333333]WT2,ABO2,ABR2,ACP2)[/COLOR]
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,209
If you're looking for the minimum date that's above a given date, you can try:

=MIN(IF(WT2>=G4,WT2,99999),IF(ABO2>=G4,ABO2,99999),IF(ABR2>=G4,ABR2,99999),IF(ACP2>=G4,ACP2,99999))

where your given date is in G4. If no date satisfies your condition, it will return 99999. Granted, this doesn't scale well, but I haven't found an array formula that works with disjoint ranges.
 

Uganda

Board Regular
Joined
Jun 10, 2015
Messages
72
They do not need to all be next to each other.
For example,
Code:
=MIN([COLOR=#333333]WT2,ABO2,ABR2,ACP2)[/COLOR]

Thanks Joe! I know that the MIN formula can contain a list however with the IF formula it wasn't working :(

My issue is that I need to pick the MIN date but it has to be after 01/07/15.

Thanks in advance
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,306
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

My issue is that I need to pick the MIN date but it has to be after 01/07/15.
Did you see Eric's response?
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Note that if you are using an actual date, you will need to put it "" or in the DATE() function, otherwise excel will think it is a calc...

=MIN(IF(A1:A4>=01/07/15, A1:A4))
would be seen as...
=MIN(IF(A1:A4>=0.00952381, A1:A4))
1 divided 7 divided 15 = 0.00952381
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Try this as an array entered with CTRL + SHIFT + ENTER

=MIN(IF(CHOOSE({1,2,3,4},WT2,ABO2,ABR2,ACP2)>"01/07/15"+0,CHOOSE({1,2,3,4},WT2,ABO2,ABR2,ACP2)))
 
Last edited:

Uganda

Board Regular
Joined
Jun 10, 2015
Messages
72
Jonmo1, thanks so much for this. I have never used the CHOOSE function and I am not sure how it works but I will read up on it! This has worked very well. Thanks, have a good evening! :)
 

Uganda

Board Regular
Joined
Jun 10, 2015
Messages
72
Thanks Eric for your help, I found the answer below from Jonmo1. The reason your solution wouldn't work is that I couldn't prioritise the columns.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,139
Messages
5,835,626
Members
430,372
Latest member
contentment

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
Top