| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding next non-blank cell
|
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding next non-blank cell
I sent a workbook with this change =IF(OR(C2<1,MIN(IF(D2:$IV2C2,D2:$IV2))-C2<1),"",MIN(IF(D2:$IV2C2,D2:$IV2))-C2) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Did you array enter as instructed? Send me a workbook to the address below. -- Don Guillett Microsoft MVP Excel SalesAid Software "Stu" wrote in message ups.com... 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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding blank cells | Excel Discussion (Misc queries) | |||
| Finding the bottom non-blank cell in a range | Excel Discussion (Misc queries) | |||
| finding blank cell and moving specific data into it | Excel Worksheet Functions | |||
| Finding first blank in column | Excel Discussion (Misc queries) | |||
| Finding the first blank or zero value | Excel Worksheet Functions | |||
![Stu[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
