Thread: Max if less than, Non-continuious array
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme[_3_] Bernard Liengme[_3_] is offline
external usenet poster
  
Posts: 1,104
Default 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?




Reply With QuoteReply With Quote