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!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
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
10,608
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
55,914
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,408
Messages
5,595,961
Members
414,035
Latest member
billbumkins

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