Weighted/Conditional LINEST in VBA

[brendanec]

Hey there. I saw this code posted, but have no idea how to modify it. It allows a linest formula to be conditional based on the value of a cell in a column. I am hoping to modify it to where i can add several conditions to look for in several columns (up to 5).

Conditional LINEST function that only finds the x coefficient if the range matches the additional criteria:

[VBA]Function LinestCond(rY As Range, rX As Range, rCond As Range, vCond As Variant, _
Optional bConst As Boolean = True, Optional bStats As Boolean = False)
Dim vY As Variant, vX As Variant
Dim lRowAll As Long, lRow As Long, lRows As Long, j As Long

lRows = Application.WorksheetFunction.CountIf(rCond, vCond)
ReDim vY(1 To lRows, 1 To 1)
ReDim vX(1 To lRows, 1 To rX.Columns.Count)

For lRowAll = 1 To rY.Rows.Count
If rCond(lRowAll) = vCond Then
lRow = lRow + 1
vY(lRow, 1) = rY(lRowAll)
For j = 1 To UBound(vX, 2)
vX(lRow, j) = rX(lRowAll, j)
Next j
End If
Next lRowAll
LinestCond = Application.WorksheetFunction.LinEst(vY, vX, bConst, bStats)
End Function

[/VBA]

Formula example:

=LinestCond($U$2:$U$56,$P$2:$P$56,$N$2:$N$56,"D") 

How can i make it look at 5 more columns and have 5 more conditions? I am hoping to learn how to achieve this, not just have someone paste the answer (though i wouldn't mind that either if that's all the time you have for!)

Thanks!

[SamT]

All I have done is change the names of the variables and added comments. I don't recommend that you trust my comments. See attachment

[vba]Option Explicit

Function LinestCond(RngOne As Range, _
RngTwo As Range, _
RngThree As Range, _
CondOne As Variant, _
Optional bConst As Boolean = True, _
Optional bStats As Boolean = False)

Dim ArrOne As Variant, ArrTwo As Variant
Dim RngIndexOne As Long
Dim ArrIndexOne As Long, ArrIndexTwo As Long
Dim RowsMetCondCnt As Long

''''Set RowsMetCondCnt = Count the Rows in RngThree that meet CondOne. .
RowsMetCondCnt = Application.WorksheetFunction.CountIf(RngThree, CondOne)

''''Set ArrOne and ArrTwo to Arrays, sized as per RowsMetCondCnt and
'Num Columns in Range RngTwo.
ReDim ArrOne(1 To RowsMetCondCnt, 1 To 1)
ReDim ArrTwo(1 To RowsMetCondCnt, 1 To RngTwo.Columns.Count)
'Two dimensional Arrays can be considered as a row column array
'where the first dimension is the number of rows and the second,
'the number of columns. IOW, Array(n, 1) would have n rows of 1 column

''''PHP Code: =LinestCond($U$2:$U$56,$P$2:$P$56,$N$2:$N$56,"D")
'RngOne = $U$2:$U$56
'RngTwo = $P$2:$P$56
'RngThree = $N$2:$N$56
'CondOne = "D"

''''Start looping thru RangeThree
For RngIndexOne = 1 To RngOne.Rows.Count
' Same as RngThree.Cells(RngIndexOne)
If RngThree(RngIndexOne) = CondOne Then
'Skip Array(0) 'Arrays are 1 based. See Redim above.
'Increment ArrIndexOne for each loop iteration
ArrIndexOne = ArrIndexOne + 1
'ArrOne(AI1, 1) = RngOne("U2").Offset(RI1, 0).Value
ArrOne(ArrIndexOne, 1) = RngOne(RngIndexOne)
'
For ArrIndexTwo = 1 To UBound(ArrTwo, 2)
'ArrTwo(Row#=AI1, Col#=AI2) = RngTwo.Cell("P2").Offset(RowNum, AI2)
ArrTwo(ArrIndexOne, ArrIndexTwo) = RngTwo(RngIndexOne, ArrIndexTwo)
Next ArrIndexTwo
End If
Next RngIndexOne 'Iterate Loop

''''Calculate LinEst and set Function Return value
LinestCond = Application.WorksheetFunction.LinEst(ArrOne, ArrTwo, bConst, bStats)
End Function

[/vba]