ExcelBanter - Finding next non-blank cell

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Finding next non-blank cell (https://www.excelbanter.com/excel-discussion-misc-queries/163001-finding-next-non-blank-cell.html)

Finding next non-blank cell

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

Finding next non-blank cell

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:$IV4K4,M4:$IV4))-K4<1,"",MIN(IF(M4:$IV4K4,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.

On 22 Oct, 15:30, "Don Guillett" wrote:
Try this ARRAY formula that must be entered using ctrl+shift+enter

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Stu" wrote in message

ups.com...

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- Hide quoted text -

- Show quoted text -

Finding next non-blank cell