Search backward (UP in column) till first non-empty cell

[aiki100]

Hello all - I hope someone might be able to lend a hand with this, please:


col A has dates.
col B uses a formula to sum numbers from a col located elsewhere


The summed numbers in col B may have been summed by one date, or over many days, the formula result being a column B of summed numbers, often with blank cells above (and below the sum) until another summed number.


What I need please, is a formula that will tel me the date (colA) associated with the MAX number found in col B, and search backwards (UP the column), till it finds the first non-empty cell and get the date from that row.


(This search should find the previous summed number from before MAX and will be one row before the start of the run that ended in MAX)


The short of it: I’m trying to find the number of days between MAX in col B and the start of the run that led to MAX in col B.


All help appreciated, thanks!

Excel 2016 (Mac) 32 bit

	A	B	C	D	E
(start Date)
(end Date)

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Max DD[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Days Between[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1/7/16[/TD]
[TD="align: center"]1.08[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]?[/TD]
[TD="bgcolor: #FFFF00"]so: formula for number of days between found 1/12/16 and1/16/16?[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1/8/16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1/8/16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1/9/16[/TD]
[TD="align: center"]0.33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1/10/16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1/11/16[/TD]
[TD="align: center"]0.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1/12/16[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #CCFFCC"]<- start of run that ended in MAX[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1/12/16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1/13/16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1/14/16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1/15/16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1/16/16[/TD]
[TD="align: center"]4.04[/TD]
[TD="bgcolor: #CCFFCC"]<-This is MAX[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]1/17/16[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]1/18/16[/TD]
[TD="align: center"]0.58[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[Eric W]

Welcome to the forum.

I'm not on a Mac, so I can't do any troubleshooting, but this works on a Windows PC:

=LOOKUP(MAX(B1:B100),B1:B100,A1:A100)-LOOKUP(2^1023,B1:INDEX(B1:B100,MATCH(MAX(B1:B100),B1:B100,0)-1),A1:A100)

 

[aiki100]

Hi Eric -

A quick test tells me you nailed it!

I wil be extrapolating to a different (larger) workbook, and expect it to work there like a champ with some minor tweaking.

Can't thank you enough - Have a fine day!

 

[Eric W]

Glad to help!

 

[aiki100]

One ore thing please:

Can you elaborate on what this part:

-LOOKUP(2^1023,B1:INDEX

...does?


Thanks again

 

[Eric W]

Sure, let me walk through the formula for you.

The first part is pretty basic:

=LOOKUP(MAX(B1:B100),B1:B100,A1:A100)

MAX(B1:B100) returns the maximum value in B1:B100, ignoring non-numeric values. Then

=LOOKUP(4.04,B1:B100,A1:A100)

finds 4.04 in the B1:B100 range, and returns the value in the corresponding position in the A1:A100 range.

Here's the second part, which finds the date corresponding to the first non-blank value above the 4.04.

LOOKUP(2^1023,B1:INDEX(B1:B100,MATCH(MAX(B1:B100),B1:B100,0)-1),A1:A100) <-- Find the maximum value in B1:B100

LOOKUP(2^1023,B1:INDEX(B1:B100,MATCH(4.04,B1:B100,0)-1),A1:A100) <-- Find the position within B1:B100 matching 4.04

LOOKUP(2^1023,B1:INDEX(B1:B100,13-1),A1:A100) <-- Back up 1 row

LOOKUP(2^1023,B1:INDEX(B1:B100,12),A1:A100) <-- Convert that position into a cell reference

LOOKUP(2^1023,B1:B12,A1:A100)

At this point, there's a feature of LOOKUP that says if it can't find the value you're looking for, it will return the location of the last value it sees, ignoring empty cells. So we give it the biggest number possible, so that we know it won't find a match, and it will return the last value it sees. (Some people use 9.99999999999999E+307 instead, which is the absolute largest value you can type into an Excel cell, but 2^1023 is nearly as big, and much shorter to type.)

So LOOKUP finds the last value in B1:B12, which is in B7, then finds the corresponding value from A1:A100, which is 1/11/2016.

Hope this helps!

 

[aiki100]

Eric: thanks so much for the detail. Yes, indeed, it helped very much: I now see how it works, AND what I need to learn more about! Especially like the 2^1023 -- very smart.

Thanks again - much appreciated!

 

[Eric W]

You're welcome.

 

[aiki100]

Eric-

Checking back in on this one if you have a moment to review:

I used the above formula on the same worksheet that contains my data, and found it to work. (Very much appreciated again, thank you!)

Then, I tried to use the same formula on a different worksheet, referencing those same fields from the other worksheet with the data (see below please).

For some oddball reason, I now return numbers in the hundreds when they were only double-digits before. Interestingly, on a few passes, I'd get a number like 376, when the the actual days I counted by hand were 37. This relationship has not been the same for all sheets I've tried this with.

Have no doubt it is something I am doing - I did duplicate the entire workbook -- and I did need to sort the worksheet, but didn't include the formula cell in the sort, (and in any event once I had deleted some cell contents, I resorted to the original sort configuration) and visual inspection shows me that the formula I expect to be there, is there as I think (dangerous, yes, I know...) it should be.

This is what it looks like now:

=LOOKUP(MAX(Data1!BW1:BW2000),Data1!BW1:BW2000,Data1!Z1:Z2000)-LOOKUP(2^1023,Data1!BW1:INDEX(Data1!BW1:BW2000,MATCH(MAX(Data1!BW1:BW2000),Data1!BW1:BW2000,0)-1),Data1!Z1:Z2000)

BW = MAXDD Data (col B in example above)
Z = Dates (col A in example above)



Any thoughts on this? Thanks so much!