brendanec
03-20-2013, 10:46 AM
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:
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
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!
Conditional LINEST function that only finds the x coefficient if the range matches the additional criteria:
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
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!