| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
| Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
| Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Linear Mode
