Solve 4 equations with 4 unknowns

EssoExplJoe

New Member
Joined
Nov 11, 2016
Messages
30
I am trying to solve for 4 unknowns in 4 equations as below. I have managed to write a VBA routine to solve for 3 unknowns with 4 equations but I am getting totally lost in the weeds trying with 4 unknowns (4 cubic equations). Does any have any VBA routines that does this?

Points: (Y1,X1), (Y2,X2), (Y3,X3), (Y4,X4)
Points: (10,2), (5,4), (4,11), (4,15)
Four Equations with 4 variables:
a) Y1=a+bX1+cX12+dX13
b) Y2=a+bX2+cX22+dX23
c) Y3=a+bX3+cX32+dX33
d) Y4=a+bX4+cX42+dX43
Solve for a,b c and d :
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
A solution using Solver


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD]
[TD="bgcolor: #DCE6F1"]
B
[/TD]
[TD="bgcolor: #DCE6F1"]
C
[/TD]
[TD="bgcolor: #DCE6F1"]
D
[/TD]
[TD="bgcolor: #DCE6F1"]
E
[/TD]
[TD="bgcolor: #DCE6F1"]
F
[/TD]
[TD="bgcolor: #DCE6F1"]
G
[/TD]
[TD="bgcolor: #DCE6F1"]
H
[/TD]
[TD="bgcolor: #DCE6F1"]
I
[/TD]
[TD="bgcolor: #DCE6F1"]
J
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
[TD]
Points​
[/TD]
[TD]
Y​
[/TD]
[TD]
X​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD]
[TD]
1​
[/TD]
[TD]
10​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD]
[TD]
2​
[/TD]
[TD]
5​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD]
[TD]
3​
[/TD]
[TD]
4​
[/TD]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD]
[TD]
4​
[/TD]
[TD]
4​
[/TD]
[TD]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7
[/TD]
[TD]
Coefs of a​
[/TD]
[TD]
Coefs of b​
[/TD]
[TD]
Coefs of c​
[/TD]
[TD]
Coefs of d​
[/TD]
[TD][/TD]
[TD]
Equations​
[/TD]
[TD]
Eq. Results​
[/TD]
[TD]
Variables​
[/TD]
[TD]
Solver Results​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
4​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD]
10,000001​
[/TD]
[TD]
10​
[/TD]
[TD]
a=​
[/TD]
[TD="bgcolor: #D9D9D9"]
18,7802226​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9
[/TD]
[TD]
1​
[/TD]
[TD]
4​
[/TD]
[TD]
16​
[/TD]
[TD]
64​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[TD]
b=​
[/TD]
[TD="bgcolor: #D9D9D9"]
-5,488346138​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10
[/TD]
[TD]
1​
[/TD]
[TD]
11​
[/TD]
[TD]
121​
[/TD]
[TD]
1331​
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
4​
[/TD]
[TD]
c=​
[/TD]
[TD="bgcolor: #D9D9D9"]
0,587412716​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11
[/TD]
[TD]
1​
[/TD]
[TD]
15​
[/TD]
[TD]
225​
[/TD]
[TD]
3375​
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
4​
[/TD]
[TD]
d=​
[/TD]
[TD="bgcolor: #D9D9D9"]
-0,019147523​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Use exactly the data layout above
-Coefficients of a
A8:A11 = 1
-Coefficients of b
B8 copied down
=$C2
-Coefficients of c
C8 copied down
=$C2^2
-Coefficients of d
D8 copied down
=$C2^3

Formula in F8 copied down
=$A8*I$8+$B8*I$9+$C8*I$10+$D8*I$11

Formula in G8 copied down
=$B2

Data > Solver
(if you cannot see Solver in tab Data, enable the add-in)
Set
By Changing Variable Cells
=$I$8:$I$11

Add a Restriction
=$F$8:$F$11 = $G$8:$G$11

Uncheck
Make Unconstrained Variables Non Negatives

click the button
Solve

M.
 
Upvote 0
Or, given:

a+2b+4c+8d=10
a+4b+16c+64d=5
a+11b+121c+1331d=4
a+15b+225c+3375d=4

MMULT(MINVERSE({1,2,4,8;1,4,16,64;1,11,121,1331;1,15,225,3375}),{10;5;4;4})

--> solutions {18.78;-5.49;0.587;-0.019} approx,

i.e. same as Marcelo's
 
Upvote 0
Great...just what I was looking for. Thanks. I'll give it a try. Actually, this is a minor part of my project that will be used to generate polynomial curves for calculating a oil well bore path to intersect multiple three dimensional targets given a surface well location and targets with North-South and East-Coordinates along with True Vertical Depths of each target. I didn't realize Excel had matrix functionality. Learn something everyday with Excel.
 
Upvote 0
How would you solve this problem if some of the unknown variables were exponents? That's the problem I encounter now!
 
Upvote 0
It has a real application, it has to do with optimal revenue thresholds for entry and exit into different projects. It's derived in the book Investments Under Uncertainty. All of the numbers in the equations above are one's I chose arbitrarily.
 
Upvote 0
Sorry, equations here

1. -A*B^2 + C*B^-3 + B/5 - 4 = 10
2. -A*D^2 + C*D^-3 + D/5 - 4 = 0
3. -2*A*C^1 + (-3*C*(B^-4)) + (1/5) = 0
4. -2*A*D^1 + (-3*C(D^-4) + (1/5) = 0
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top