Cascade IF's

[arogers]

I am trying to use cascading if's to make my spreadsheet more automated. The idea is to automatically update a days pending cell as a project moves through different stages.

Here is my code:

=IF(M17="",DAYS360(L17,NOW()),IF(L17="",DAYS360(J17,NOW()),IF(J17="",DAYS360(H17,NOW()),IF(H17="",DAYS360(F17,NOW()),IF(F17="",DAYS360(E17,NOW()),IF(E17="",DAYS360(D17,NOW()),IF(D17="",D17,DAYS360(D17,NOW()))))))))

D column represents oldest date value and M represents most recent date value.

 

[Aladin Akyurek]

Does the following

=DAYS360(LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5,6},D17,E17,F17,H17,J17,L17)),TODAY())

deliver the good you are after?

 

[Ron Coderre]

Try this regular formula:

=DAYS360(MAX(IF(ISNUMBER(MATCH(COLUMN(D17:M17),{4,5,6,8,10,12,13},0)),D17:M17)),TODAY())

Is that something you can work with?

 

[arogers]

Try this regular formula:

=DAYS360(MAX(IF(ISNUMBER(MATCH(COLUMN(D17:M17),{4,5,6,8,10,12,13},0)),D17:M17)),TODAY())

Is that something you can work with?

Yes! This worked! Can you explain the code so I can apply the same principle to other parts of my worksheet?

 

[Aladin Akyurek]

LOOKUP. formula should also work. If M7 is also a possible target for DAYS360, you can add that to the CHOOSE bit.

 

[Ron Coderre]

For this example, I entered first-of-month dates for Jan through Apr in D17, E17, F17, AND H17
In this formula (for today, 29-Aug-2014):
=DAYS360(MAX(IF(ISNUMBER(MATCH(COLUMN(D17:M17),{4,5,6,8,10,12,13},0)),D17:M17)),TODAY())
The IF function section checks if the referenced column numbers match the list of column numbers I'm interested in.
If yes, it returns the date from the corresponding cells (or false if the cell is blank)
=DAYS360(MAX(IF(ISNUMBER(MATCH({4,5,6,7,8,9,10,11,12,13},{4,5,6,8,10,12,13},0)),D17:M17)),TODAY())
=DAYS360(MAX(IF(ISNUMBER({1,2,3,#N/A,4,#N/A,5,#N/A,6,7}),D17:M17)),TODAY())
=DAYS360(MAX({41640,41671,41699,FALSE,41730,FALSE,0,FALSE,0,0}),TODAY())
=DAYS360(41730,41880)
=148

I hope that helps.

 

[Ron Coderre]

Even if that formula is amended to include M17:

=DAYS360(LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3,4,5,6,7},D17,E17,F17,H17,J17,L17,M17)),TODAY())

it still returns date serial numbers if any of the last referenced cells are blank.

The CHOOSE function returns zeros for blank cells
If L17 and M17 are blank, the LOOKUP function returns zero...causing the DAYS360 function to return the date serial number for TODAY()
instead of the day difference between the latest date and today.

However, if you use this approach...it works:

=DAYS360(MAX(CHOOSE({1,2,3,4,5,6,7},D17,E17,F17,H17,J17,L17,M17)),TODAY())

 

[Aladin Akyurek]

...
However, if you use this approach...it works:

=DAYS360(MAX(CHOOSE({1,2,3,4,5,6,7},D17,E17,F17,H17,J17,L17,M17)),TODAY())

That's ok with me

 

[arogers]

Thanks for you help! It's starting to make sense, but where do you get the numbers for the array?

 

[Aladin Akyurek]

Thanks for you help! It's starting to make sense, but where do you get the numbers for the array?

CHOOSE assembles the values of the various cell into an array constant, something like:

[1;5;0.9;56;....}