Finding the next lowest date

[hhebe]

Good morning,

I need to be able to search an entire row and return the next smallest date. I was originally just using =IFERROR(IF(AND(AJ4>1,AH4<>"N/A"),AJ4-AH4,IF(AND(AJ4>1,AF4<>"N/A"),AJ4-AF4,IF(AND(AJ4>1,AD4<>"N/A"),AJ4-AD4,IF(AND(AJ4>1,AB4<>"N/A"),AJ4-AB4,IF(AND(AJ4>1,Z4<>"N/A"),AJ4-Z4,""))))),"")

I have a long list of steps that have to be completed and between each one I have a days to complete column. The steps are usually done in order but not always and are sometimes done concurrently.

Currently I have it set up using the sample formula above, which would take the date that step was completed and subtract the next date to the left, skipping blanks. I need for the formula to search the whole row and find the next lowest date based off of the date inserted in the column to the formulas left.

I have a sample sheet I can email if needed. Thanks in advance

This is how I want it to read, ignoring blanks:

Test

Pop Size

Step 1

Step 2

Days to Complete

Step 3

Days to Complete

Step 4

Days to Complete

Step 5

Days to Complete

Step 6

Days to Complete

Jan 2014

35

9/15/14

9/16/14

1

9/19/14

1

9/18/14

2

9/25/14

6

Feb 2014

88

10/1/14

10/1/14

10/12/14

3

10/9/14

8

10/13/14

1

10/16/14

3

Mar 2014

47

11/6/14

11/8/14

2

11/9/14

1

11/10/14

1

11/15/14

5

11/17/14

2

Apr 2014

36

12/1/14

12/2/14

1

12/6/14

1

12/7/14

1

12/5/14

3

12/10/14

3

<TBODY>
</TBODY>

 

[hhebe]

I have tried using match index but I read that is mainly for vertical lookups and has to be decending order. Any tips or questions?

 

[Asala42]

Try this array formula:

{=MIN(IF(A4:M4>29221,A4:M4))}

Hit Ctrl+Shift+Enter to get the brackets.

The 29221 reference is the numeric equivalent of 1/1/1980. Basically it's taking the minimum value in the range where the value is larger than that (you could probably substitute that with any number that exceeds any reasonable number of days to complete).

 

[hhebe]

I'm not getting it to work but even still I don't think the logic is quite what I'm looking for. I tried replacing the 29921 with the reference cell and that wouldn't work. I don't need the smallest number of the whole array but the the one that is the closest to the reference but smaller.

Does that make any sense?

 

[hhebe]

I've been researching and toying for days now and still haven't gotten it. With arrays I've run into the problem that I have the Days to Complete on the same row as the data


The dates are listed out horizontally and between each step is a count of days that it took to finish that step based off of the difference between that date and the next highest (or is it lowest?) date that is on that same row. IE Ste
p 2 was completed 9/16/14 and the previous date is 9/15/14 so it took one day to complete; step 3 was completed 9/19/14 but instead of taking 3 days to complete, based off of step 2, I need it to say 1 day complete, because there was another step which was completed 9/18/14. It may come down to just not being able to have the days to complete and the dates on the same row but I'd like to try to keep it this way.

Test Pop Size Step 1 Step 2 Days to Complete Step 3 Days to Complete Step 4 Days to Complete Step 5 Days to Complete Step 6 Days to Complete Jan 2014 35 9/15/14 9/16/14 1 9/19/14 1 9/18/14 2 9/25/14 6 <TBODY> </TBODY>



<TBODY>
</TBODY>

 

[Asala42]

I think you just need a variant of the above formula then.

The original:

{=MIN(IF(A4:M4>29221,A4:M4))}

Was just pulling the smallest date it could find, provided it was after 1/1/1980. The 29221 was mostly to ignore the smaller numeric "Days to compete" measures mixed in with the dates. In short I just misread your question.

How about this:

{=MAX(IF(A4:M4< Z1,A4:M4))}

Using Z1 as an example reference date, this will pull the largest date it can find that's smaller than Z1.

Sidenote: I had to insert an artificial space between the < and Z1 for the forum to not truncate the formula. Just remove that space and the formula should work as intended - again with a CTRL+SHIFT-ENTER to get the brackets.

 

[hhebe]

Great, thank you Asala42! I've gotten the arrays to work by creating blank rows underneath each occupied row and housing the formulas there. Now that I know that the arrays work, do you know of any way that I can find the difference between the array and the reference date and keep it in the reference row? I've running into circular reference errors. Is there any way to apply the subtraction in the array itself?

 

[Asala42]

I think you better break up each formula then so it does not include the formula itself or other dependent formulas.

For example to exclude E:F

{=MAX(IF(A4:D4< Z1,A4:D4),IF(G4:M4< Z1,G4:M4))}