# Calculate results from vertical range and horizontal range

#### FryGirl

##### Well-known Member
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)

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
AB
1Coefficients
2Intercept-23.08
3X19.499
4X21.979
Sheet1

Book1 (Recovered).xlsb
DEFGHIJKLM
1X1X2X3X4X5X6X7X8X9X10
2756581761794835630
Sheet1

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=B2+SUMPRODUCT(B3:B12,TRANSPOSE(D2:M2))``

#### FryGirl

##### Well-known Member
Thank you Fluff. That will do it.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
7
Views
184
Replies
3
Views
109
Replies
8
Views
93
Replies
4
Views
195
Replies
2
Views
63

1,126,893
Messages
5,621,484
Members
415,844
Latest member

### 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.

### Which adblocker are you using?

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

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