Creating Range with a Variable Starting Point

[ChrisRRR]

I am trying to eliminate volatile/single thread functions to make my sheet run faster. The transition is proving difficult in many aspects for me.

I have many numeric values that are variable in Col B up to row 214. They continuously descend in value. An entry can be made that will add to a cell in Col B, but the numbers will still continuously descend afterwards.

A1 contains the number 140. I am waiting for the numbers in Col B to drop just below A1.

After an entry is made that causes an addition in a Col B cell, there is no way to determine how long it will take for the numbers to drop to A1.

Currently, I can get the row number of the last entry with this in cell A2. Col C determines when an addition is made to Col B.

A2=LOOKUP(2,1/(C5:C214<>""),ROW(B5:B214))


I can also get the value of the Col B cell after an addition with this A3=INDEX(B1:B214,A2)

My questions is how do I make a formula that will search Col B from the last time an addition was made to the end of Col B for a number just below A1? Note: It is possible for Col B to drop below A1 more than once, so it is important that this checks Col B after the last increase was made.

 

[offthelip]

I am not clear what you are trying to do, but it sounds as though it would be much easier to write a bit of VBA and put it in the worksheet change or the worksheet calculate event to do what you want. If the VBA is written properly it would be much faster than using worksheet functions. Without seeing your worksheet it is difficult to give any real assistance.

 

[ChrisRRR]

I am not clear what you are trying to do, but it sounds as though it would be much easier to write a bit of VBA and put it in the worksheet change or the worksheet calculate event to do what you want. If the VBA is written properly it would be much faster than using worksheet functions. Without seeing your worksheet it is difficult to give any real assistance.

I'm not very well versed with VBA. I am in the process of teaching myself.

A1 is matched against Col B to return the first value lower. There are three values that match B5,B9,B13. I want B13 returned because there are no new entries past C11.
Notice that an entry in C results in an increase in B7 and B11.

All values are subject to change by the user as there is no pattern.

The formulas I posted earlier were my attempt to solve this issue. Inside A2, I put LOOKUP(2,1/(C5:C214<>""),ROW(B5:B214)) which returns 11. That is the last row an entry was made in C. Inside A3, I put INDEX(B1:B214,A2) which returns 176, also in row 11.

My thoughts were that I could create a range of B11 (11 because I can get that row number):B214. I'm not sure how I can create the range, but ultimately in this example below, I want a formula to return B13.


[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]140[/TD]
[TD]514[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]430[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]315[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]139[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]343[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]203[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]127[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]115[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]176[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]153[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]135[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]121[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]118[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]95[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]85[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]76[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]54[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ChrisRRR]

After some looking around on the internet, I found some threads that are about finding the last match in a range. The only problem is that they typically focus on words ort exact matches. This is different because an exact match is not possible. The next biggest value less than is what I'm looking for

 

[Marcelo Branco]

Not sure i understand what you are trying to do.

See if this does what you need

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

140​

[/td][td]

514​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

11​

[/td][td]

430​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

135​

[/td][td]

315​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td]

200​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td]

139​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td]

125​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td]

343​

[/td][td]

x​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td]

203​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td]

127​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td]

115​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td]

176​

[/td][td]

x​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td]

153​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td]

135​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td]

121​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td]

118​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td][/td][td]

95​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td][/td][td]

85​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td][/td][td]

76​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td][/td][td]

54​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

20​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in A2
=LOOKUP(2,1/(C1:C214="x"),ROW(B1:B214))

Formula in A3
=INDEX(B:B,MATCH(1,INDEX((A1>B1:B214)*(ROW(B1:B214)>A2),),0))

M.

 

[ChrisRRR]

Not sure i understand what you are trying to do.

See if this does what you need



Formula in A2
=LOOKUP(2,1/(C1:C214="x"),ROW(B1:B214))

Formula in A3
=INDEX(B:B,MATCH(1,INDEX((A1>B1:B214)*(ROW(B1:B214)>A2),),0))

M.

Marcelo, thank you for coming to the rescue again. That is exactly what I needed. I'm not exactly sure how some of it works, but it does the trick