FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,364
- Office Version
- 365
- 2016
- Platform
- Windows
If have two ranges. The first range is B2:B12 and when used against the UDF below, it produces Y = [-23.08 + 9.499(X1) + 1.979(X2)]
=BuildEquation(B3:B12,B2)
The second range runs D2:M2
The above equation is one piece I need, but now the other is the actual mathematical answer. Right now I've used this formula
=B2+B3*D2+IF($B$4<>"",$B$4*E2,0)+IF($B5<>"",$B5*F2,0)+IF($B6<>"",$B6*G2,0)+IF($B7<>"",$B7*H2,0)+IF($B8<>"",$B8*I2,0)+IF($B9<>"",$B9*J2,0)+IF($B10<>"",$B10*K2,0)+IF($B11<>"",$B11*L2,0)+IF($B12<>"",$B12*M2,0)
but was hoping there was something more succinct to use whether it be another UDF or an alternative formula. The answer with this data is 817.98
=BuildEquation(B3:B12,B2)
VBA Code:
Function BuildEquation(rng As Range, Rng2 As Range) As String
Dim Cl As Range
For Each Cl In rng
If Cl.Value <> "" Then
BuildEquation = BuildEquation & Cl.Value & "(" & Cl.Offset(0, -1).Value & ")" & " + "
End If
Next Cl
BuildEquation = "Y = [" & Rng2.Value & " + " & Replace(Left(BuildEquation, Len(BuildEquation) - 3), "(a)", "") & "]"
End Function
The second range runs D2:M2
The above equation is one piece I need, but now the other is the actual mathematical answer. Right now I've used this formula
=B2+B3*D2+IF($B$4<>"",$B$4*E2,0)+IF($B5<>"",$B5*F2,0)+IF($B6<>"",$B6*G2,0)+IF($B7<>"",$B7*H2,0)+IF($B8<>"",$B8*I2,0)+IF($B9<>"",$B9*J2,0)+IF($B10<>"",$B10*K2,0)+IF($B11<>"",$B11*L2,0)+IF($B12<>"",$B12*M2,0)
but was hoping there was something more succinct to use whether it be another UDF or an alternative formula. The answer with this data is 817.98
Book1 (Recovered).xlsb | ||||
---|---|---|---|---|
A | B | |||
1 | Coefficients | |||
2 | Intercept | -23.08 | ||
3 | X1 | 9.499 | ||
4 | X2 | 1.979 | ||
Sheet1 |
Book1 (Recovered).xlsb | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
D | E | F | G | H | I | J | K | L | M | |||
1 | X1 | X2 | X3 | X4 | X5 | X6 | X7 | X8 | X9 | X10 | ||
2 | 75 | 65 | 81 | 76 | 1 | 79 | 48 | 3 | 56 | 30 | ||
Sheet1 |