Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Nested IF - Work Around for 7 nest Max
I have consecutive dates in column B and integers in column D. In column B,
there can be anywhere from 0 to 10 dates listed and in column D, there may or may not be an integer associated with it. If column B is empty, column D will also be empty. I need to populate a cell with the date in column B that matches the last integer in Column D. For Example, if I have B1:1/1/04, B2:1/2/04, B3:1/3/04, B4:1/4/04 and D1:900, D2:800, D3:700, D4: Empty. I'm looking for a formula that will return 1/3/04 Previously, my maximum rows was 5 and I had this formula: =IF(D5<0,B5,IF(D4<0,B4,IF(D3<0,B3,IF(D2<0,B2,I F(D1<0,B1,"n/a"))))) Now I'm being asked to expand this for 10 rows and with the 7 nested limitation, I'm unable to. Is there a different formula I can use or a way to get around this? |
#2
|
|||
|
|||
=INDEX(B1:B100,COUNTA(D1:D100))
-- HTH RP (remove nothere from the email address if mailing direct) "AEICHEN" wrote in message ... I have consecutive dates in column B and integers in column D. In column B, there can be anywhere from 0 to 10 dates listed and in column D, there may or may not be an integer associated with it. If column B is empty, column D will also be empty. I need to populate a cell with the date in column B that matches the last integer in Column D. For Example, if I have B1:1/1/04, B2:1/2/04, B3:1/3/04, B4:1/4/04 and D1:900, D2:800, D3:700, D4: Empty. I'm looking for a formula that will return 1/3/04 Previously, my maximum rows was 5 and I had this formula: =IF(D5<0,B5,IF(D4<0,B4,IF(D3<0,B3,IF(D2<0,B2,I F(D1<0,B1,"n/a"))))) Now I'm being asked to expand this for 10 rows and with the 7 nested limitation, I'm unable to. Is there a different formula I can use or a way to get around this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
How to copy the work sheets from different workbooks into one? | Excel Discussion (Misc queries) | |||
How to get saved old saved work that was saved over? | Excel Discussion (Misc queries) | |||
Nested if, sum & vlookup Function | Excel Worksheet Functions | |||
Change Path names in copied work book | Excel Worksheet Functions |