Max if less than, Non-continuious array
 

I have dates in cells A1, C1, and E1. Cell F1 = 1/1/2009.

What formula can I use to find the maximum of cells A1, C1, and E1 IF AND
ONLY IF the date is is earlier than F1?

Max if less than, Non-continuious array
 

=IF(TODAY()<F1,MAX(A1,C1,E1),"")
--
If this post helps click Yes
---------------
Jacob Skaria


"ToddEZ" wrote:

I have dates in cells A1, C1, and E1. Cell F1 = 1/1/2009.

What formula can I use to find the maximum of cells A1, C1, and E1 IF AND
ONLY IF the date is is earlier than F1?

Max if less than, Non-continuious array
 

The formula =MAX(A1*(A1<F1),C1*(A1<F1),E1*(E1<F1))
will give the maximum of the three that is earlier than F1
You may see a three digit number like 39500, just format the cell to show a
date

If none are earlier than you get 1/1/1900. To avoid this, use
=IF(AND(A1F1,C1F1,E1F1),"None",MAX(A1*(A1<F1),C 1*(A1<F1),E1*(E1<F1)))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ToddEZ" wrote in message
...
I have dates in cells A1, C1, and E1. Cell F1 = 1/1/2009.

What formula can I use to find the maximum of cells A1, C1, and E1 IF AND
ONLY IF the date is is earlier than F1?

Max if less than, Non-continuious array
 

How about: =IF(MAX(A1,C1,E1)<F1,MAX(A1,C1,E1),"")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"ToddEZ" wrote:
I have dates in cells A1, C1, and E1. Cell F1 = 1/1/2009.

What formula can I use to find the maximum of cells A1, C1, and E1 IF AND
ONLY IF the date is is earlier than F1?

Max if less than, Non-continuious array
 

I appreciate the help, but this dosen't seem to work.

For example: A1=1/1/05, C1=5/15/06, E1=7/12/2009, F1=1/1/2009.

I am looking for a formula that will yeild the answer 5/15/06.

"Jacob Skaria" wrote:

=IF(TODAY()<F1,MAX(A1,C1,E1),"")
--
If this post helps click Yes
---------------
Jacob Skaria


"ToddEZ" wrote:

I have dates in cells A1, C1, and E1. Cell F1 = 1/1/2009.

What formula can I use to find the maximum of cells A1, C1, and E1 IF AND
ONLY IF the date is is earlier than F1?

Max if less than, Non-continuious array
 

That seems to work. Thanks!

One quick followup...

I have some text mixed in with my dates "NF" or "None"... the formula
dosen't seem to work when there is text in one of the cells. ...is there any
work-around?

THANKS AGAIN!

"Bernard Liengme" wrote:

The formula =MAX(A1*(A1<F1),C1*(A1<F1),E1*(E1<F1))
will give the maximum of the three that is earlier than F1
You may see a three digit number like 39500, just format the cell to show a
date

If none are earlier than you get 1/1/1900. To avoid this, use
=IF(AND(A1F1,C1F1,E1F1),"None",MAX(A1*(A1<F1),C 1*(A1<F1),E1*(E1<F1)))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ToddEZ" wrote in message
...
I have dates in cells A1, C1, and E1. Cell F1 = 1/1/2009.

What formula can I use to find the maximum of cells A1, C1, and E1 IF AND
ONLY IF the date is is earlier than F1?

Max if less than, Non-continuious array
 

Try...

=MAX(IF(A1<F1,A1),IF(C1<F1,C1),IF(E1<F1,E1))

--
Domenic
http://www.xl-central.com


In article ,
ToddEZ wrote:

That seems to work. Thanks!

One quick followup...

I have some text mixed in with my dates "NF" or "None"... the formula
dosen't seem to work when there is text in one of the cells. ...is there any
work-around?

THANKS AGAIN!

"Bernard Liengme" wrote:

The formula =MAX(A1*(A1<F1),C1*(A1<F1),E1*(E1<F1))
will give the maximum of the three that is earlier than F1
You may see a three digit number like 39500, just format the cell to show a
date

If none are earlier than you get 1/1/1900. To avoid this, use
=IF(AND(A1F1,C1F1,E1F1),"None",MAX(A1*(A1<F1),C 1*(A1<F1),E1*(E1<F1)))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ToddEZ" wrote in message
...
I have dates in cells A1, C1, and E1. Cell F1 = 1/1/2009.

What formula can I use to find the maximum of cells A1, C1, and E1 IF AND
ONLY IF the date is is earlier than F1?

Max if less than, Non-continuious array
 

Hi,

You may also try this

=if(and(A1<F1,C1<F1,E1<F1),max(A1,C1,E1),"")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"ToddEZ" wrote in message
...
I have dates in cells A1, C1, and E1. Cell F1 = 1/1/2009.

What formula can I use to find the maximum of cells A1, C1, and E1 IF AND
ONLY IF the date is is earlier than F1?