Range defining problem

didijaba

Well-known Member
Joined
Nov 26, 2006
Messages
511
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,
 

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
Joined
Apr 11, 2010
Messages
11,005
do you want to find the first number below 20 and from there, the first number >= 20

25
17
13
23
18
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

25
29
171
10
11
14
212
22
24
33
171
12
13
14
292
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
Joined
Nov 26, 2006
Messages
511
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?
 
Master Excel Bundle

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.

Forum statistics

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

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?

Disable AdBlock

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
Top