Cascade IF's

arogers

New Member
Joined
Aug 28, 2014
Messages
6
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?
 
Upvote 0
Try this regular formula:
Code:
=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?
 
Upvote 0
Try this regular formula:
Code:
=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?
 
Upvote 0
LOOKUP. formula should also work. If M7 is also a possible target for DAYS360, you can add that to the CHOOSE bit.
 
Upvote 0
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.
 
Upvote 0
Even if that formula is amended to include M17:
Code:
=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:
Code:
=DAYS360(MAX(CHOOSE({1,2,3,4,5,6,7},D17,E17,F17,H17,J17,L17,M17)),TODAY())
 
Upvote 0
Thanks for you help! It's starting to make sense, but where do you get the numbers for the array?
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top