Max if less than, Non-continuious array

T

ToddEZ

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?
 
B

Bernard Liengme

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(A1>F1,C1>F1,E1>F1),"None",MAX(A1*(A1<F1),C1*(A1<F1),E1*(E1<F1)))

best wishes
 
B

Bernard Liengme

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(A1>F1,C1>F1,E1>F1),"None",MAX(A1*(A1<F1),C1*(A1<F1),E1*(E1<F1)))

best wishes
 
T

ToddEZ

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.
 
T

ToddEZ

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.
 
T

ToddEZ

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!
 
T

ToddEZ

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

compare three cells 1
Combining several cells into single equasion? 1
COMPARING TIMES AND DISPLAYING TEXT USING IF CONDITION 3
gain-loss 5
Rolling totals 1
Spurious Formula Omits Adjacent Cells 2
I'd like to know how we can count coloured cells? 2
Counting coloured cells? 1

Top