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,
 

Some videos you may like

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

25
17
13
23
18
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,157
Messages
5,527,128
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top