Solve 4 equations with 4 unknowns

[EssoExplJoe]

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: (Y₁,X₁), (Y₂,X₂), (Y₃,X₃), (Y₄,X₄)
Points: (10,2), (5,4), (4,11), (4,15)
Four Equations with 4 variables:
a) Y₁=a+bX₁+cX₁²+dX₁³
b) Y₂=a+bX₂+cX₂²+dX₂³
c) Y₃=a+bX₃+cX₃²+dX₃³
d) Y₄=a+bX₄+cX₄²+dX₄³
Solve for a,b c and d :

 

[MARK858]

Cross-posted without a link here

 

[Marcelo Branco]

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.

 

[StephenCrump]

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

 

[Marcelo Branco]

Stephen,

Nice! Well done.

M.

 

[EssoExplJoe]

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.

 

[JoeJackson12]

How would you solve this problem if some of the unknown variables were exponents? That's the problem I encounter now!

 

[StephenCrump]

How would you solve this problem if some of the unknown variables were exponents?

Can you post an example? Does it have real application, or is it just a mathematical exercise?

 

[JoeJackson12]

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.

 

[JoeJackson12]

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