# 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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### oldbrewer

##### 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

#### 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

##### Well-known Member

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

##### Well-known Member
didijaba - I asked a question, I am trying to help you

#### didijaba

##### Well-known Member
didijaba - I asked a question, I am trying to help you

Replies
10
Views
146
Replies
2
Views
56
Replies
12
Views
280
Replies
2
Views
93
Replies
1
Views
117

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,712
Messages
5,833,266
Members
430,200
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back