I am stuck
Posted by Jason on September 23, 2001 10:02 PM
I have a row of due dates and a blank cell next to each date reserved for the paid date. I am looking for a formula that can test which cell is blank(meaning that it is unpaid) and show the due date. I tried the if function but it limits me to about 8 tests. I used =if(a3=0,a2,if(a5=0,a4,etc... ! There has to be an easier way to do it while keeping my current format in rows {unpaid duedate# displayed here}(duedate1)(paid1)(duedate2)(paid2)etc...
Thank you.
Posted by Rob Jackson on September 24, 2001 12:31 AM
you could find the last pay date with the MAX function.
=MAX(C2,E2,G2,I2)
this would then return the most recent date.
You could then use the MATCH and OFFSET functions to return the relevent due date.
use MATCH to find the position of the MAX date in the row and then use OFFSET to retrieve the dates either side to see if it is overdue or not. May need to test against the =TODAY() function to see if overdue.
R.
Posted by Ian on September 24, 2001 6:15 AM
Could you post on eg of the table please (NT)
Posted by Aladin Akyurek on September 24, 2001 9:24 AM
Jason,
If my understanding is correct, the following would do the job.
Lets assume the sample of data in A2:H2 below:
{36932,"",36991,"",37054,234.45,37106,""}
where the integers are dates and blanks (that is, "") meaning "unpaid".
Keep the last cell in mind. In the above sample, it's $H2.
In I2 array-enter: =IF(COUNTBLANK(A2:H2)>0,INDIRECT(ADDRESS(ROW(),MIN(IF(ISBLANK(A2:H2)*(COLUMN(A2:H2))>0,COLUMN(A2:H2)))-1)),"")
This will give you the oldest date, if any, on which the payment is overdue.
In J2 enter: =IF(COUNTBLANK($A$2:$H$2)>=COUNT($I$2:I2),ADDRESS(ROW(),MATCH(I2,$A$2:$H$2,0)+2,8),"")
In K2 array-enter: =IF(LEN(J2)>0,INDIRECT(ADDRESS(ROW(),MIN(IF((ISBLANK(INDIRECT(J2):$H2)*COLUMN(INDIRECT(J2):$H2)>0)*(COLUMN(INDIRECT(J2):$H2))>0,COLUMN(INDIRECT(J2):$H2)))-1)),"")
This will give you the second oldest date, if any, on which the payment is overdue.
And, this is important, select J2:K2 then copy across as far as needed.
Select I2 up to the last cell to which you applied copying at the previous step and copy down as far as needed.
All this was rather complex to figure out. If there is something unexpected, let me know.
Aladin
==========
Posted by Jason on September 24, 2001 4:13 PM
Perfect explanation
This solution works perfect. Initially I forgot to press crtl+shift+enter so i was returned #value, but after reviewing the formula which looked normal it worked. Thank you for your help and the email really! help.
