Range defining problem

[didijaba]

Hello,
I know this must be simple, but I am stuck and can't see tree from forrest . I have column where I have numeric values that go from 1 to 100. What I need to do is create formula that will take for 1) find where is number in column smaller than 20 , 2) create range based on that 3) if number is smaller then 20 create new range just for this value 4) create if formula using ranges created / defined.
formula should look something like =if(B24>20;TREND(B24:B30;B24) where for example range B1:B24 would be first range in column where values are bigger then 20. If then B25 and B26 would be smaller then 20 new range would be something like B27:B55.
Thanks in advance,

 

[oldbrewer]

do you want to find the first number below 20 and from there, the first number >= 20

25
17
13
23
18

 

[didijaba]

do you want to find the first number below 20 and from there, the first number >= 20

25
17
13
23
18

yes

 

[didijaba]

I can send you link to file if you want.

 

[oldbrewer]

25
29
17	1
10
11
14
21		2
22
24
33
17	1
12
13
14
29		2
39
49
39
59
these 2 helper columns find all the start and end points
can you work with this

<colgroup><col span="7"></colgroup><tbody>
</tbody>

 

[didijaba]

Option Explicit

Public Function HigherThanMe(rng As Range) As String
Dim cell As Range
On Error Resume Next
For Each cell In rng
On Error Resume Next
If cell.Value <= 20 Then   
HigherThanMe = cell.Address(0, 0, External:=False)
Exit For
End If
On Error GoTo 0
Next
End Function

Sub TEST_20()
Dim i, j, x As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LastRow
x = Range(HigherThanMe(Range(Cells(i, 1), Cells(LastRow, 1)))).Row
Cells(i, 3) = WorksheetFunction.Trend(Range(Cells(i, 2), Cells(x, 2)))
Next i
End Sub

This is what I was trying to describe. Does anyone have idea how to this using formulas in sheet?

 

[oldbrewer]

didijaba - I asked a question, I am trying to help you

 

[didijaba]

didijaba - I asked a question, I am trying to help you

sorry, yes please.