Don't understand this range reference

Inclement

New Member
From this post:
Code:
=$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).
 

Inclement

New Member
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

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

Code:
1/($d$1:$d3<>"")
to then having 1 be divided by them, thus forcing them to be numbers {1,0,1}
 

Inclement

New Member
...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!
 

Some videos you may like

This Week's Hot Topics

Top