Finding next non-blank cell

[Stu]

Hello

I have a ss contains process data with several columns of dates. As a
product goes through each process the date is captured. I now need to
analyse how long it took between each process step but the problem is
that not every product goes through all processes so I need to look at
the next non-blank one. I can do bits with nested IFs but there are
too many possibilities and the nesting is limited (plus I think there
must be a better way of doing it. I'd like to insert a column between
each date and have look at the data in the previous cell and calculate
the difference to the next non-blank date.

The data is structured -

Product Qty Date started Date1 Date2 Date3 Date4
----> Date29

ABC 5 15/10/07 16/10/07 17/10/07
18/10/07 22/10/07
XYX 4 16/10/07
19/10/07 20/10/07
ASD 20
20/10/07 20/10/07
21/10/07
LKJ 15 17/10/07
18/10/07 22/10/07

 

[Don Guillett]

Try this ARRAY formula that must be entered using ctrl+shift+enter

=IF(MIN(IF(D4:$IV4>C4,D4:$IV4))-C4<1,"",MIN(IF(D4:$IV4>C4,D4:$IV4))-C4)

 

[Stu]

Thanks Don but I'm afraid I can't get it to work, I amended your
formula to work with the ranges in my ss but it returns null. This is
the formula I derived from yours and below is the one I'm trying to
replace

=IF(MIN(IF(M4:$IV4>K4,M4:$IV4))-K4<1,"",MIN(IF(M4:$IV4>K4,M4:$IV4))-
K4)

=IF(ISBLANK(M4), IF(ISBLANK(O4), IF(ISBLANK(Q4), IF(ISBLANK(S4),
IF(ISBLANK(U4), IF(ISBLANK(W4),SUM(Y4-K4),SUM(W4-K4)), SUM(U4-K4)),
SUM(S4-K4)), SUM(Q4-K4)), SUM(O4-K4)), SUM(M4-K4))

Mine works but I need to test every other cell up to AM and can't
because of the nesting limitations.

 

[Don Guillett]

Did you array enter as instructed?
Send me a workbook to the address below.

 

[Don Guillett]

I sent a workbook with this change
=IF(OR(C2<1,MIN(IF(D2:$IV2>C2,D2:$IV2))-C2<1),"",MIN(IF(D2:$IV2>C2,D2:$IV2))-C2)