# Range defining problem

#### didijaba

##### Well-known Member
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.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### oldbrewer

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

25
17
13
23
18

#### didijaba

##### Well-known Member
do you want to find the first number below 20 and from there, the first number >= 20

25
17
13
23
18

yes

#### didijaba

##### Well-known Member
I can send you link to file if you want.

#### oldbrewer

##### Board Regular

 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

##### Well-known Member
Code:
``````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
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?

Replies
4
Views
30
Replies
7
Views
126
Replies
3
Views
28
Replies
3
Views
131
Replies
17
Views
270