Don't understand this range reference

[Inclement]

From this post:

=$b4+lookup(2,1/($d$1:$d3<>""),$d$1:$d3)

wbd

The OP's question was this:

I want a formula that would do the following

In cell D4 add D3+B4, but if D3 is blank then find the previous non blank cell and add B4 to that cell (In this case D1).

I would need these to work regardless of the number of blanks between.

Thanks

Why does the lookup range start with "1/"? What does it do? All I can think is that it really is meant to be a reciprocal, because the OP wants the previous non-blank cell - ? I experimented with removing "1/" and got an #N/A error.

Also, if anyone can point me to more information about this "1/" thing, or about using logical expressions with ranges, as WBD did with $d$1:$d3<>"", I'd appreciate it. Tried Googling, didn't find anything (probably due to poor search terms).

 

[Kaper]

It's quite common trick - note that we are looking for some value >1 (2 in our case).

I think it is easy to understand if you follow the description in "[FONT=&quot]Look up a value of the last non-blank cell in a column[/FONT]" section of https://www.ablebits.com/office-addins-blog/2017/04/05/excel-lookup-function/

 

[Inclement]

Ohhh - I was looking at it through normal-formula glasses, and it's a whole different "thing," so to speak, at a level of Excel I've never seen before. Erp.

Okay, well, I will go through that article with a fine-toothed comb and see where I get. Thank you!

 

[Michael M]

the 1 at the beginning of this section of the formula "forces" the results from, for example, {TRUE,FALSE,TRUE}

1/($d$1:$d3<>"")

to then having 1 be divided by them, thus forcing them to be numbers {1,0,1}

 

[Inclement]

...and then the 2 in lookup(2 goes through the {1,0,1} set, never finds an exact match, so it matches the last occurrence in the set of the biggest number that's smaller than it (whew), which in this case would be the second 1 - ? And then it looks to lookup's last argument (range) to know what to display in the cell -?

Is that right?

Thank you!