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?

=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?