CODEWITHSUNDEEP
excelvba
David
David

Reputation: 1293

Equation to solve for x

I have the following equation in a string 

y = 18774x + 82795

Solving for x I would do this:-

x = (y-82795) / 18774

I know the value of y

However the equation changes all the time and is always in a string format

Is it possible to simply throw the original equation at an Evaluate statement and have it solve for x automatically?

I know I can do things like this:-

MsgBox Evaluate("5*(8+3)-2*(3*5)")

but in my case it would be

MsgBox Evaluate("67657657 = 18774x + 82795")

where y = 67657657

This obviously throws an error. Is there an easy way I can solve for x?

Otherwise I would need to parse the string and work it out more manually

Upvotes: 2

Views: 1057

Answers (3)

brettdj
brettdj

Reputation: 55692

Given you are solving a linear equation:

Sub Extract()
Dim strFunc As String
Dim X(1 To 2) As Variant
Dim Y(1 To 2) As Variant
Dim C As Variant
X(1) = 0
X(2) = 100
strFunc = "18774x + 82795"
Y(1) = Evaluate(Replace(LCase$(strFunc), "x", X(1)))
Y(2) = Evaluate(Replace(LCase$(strFunc), "x", X(2)))
C = Application.WorksheetFunction.LinEst(Y, X)
MsgBox "K is " & C(1) & vbNewLine & "M is " & C(2)
End Sub

Upvotes: 0

John Alexiou
John Alexiou

Reputation: 29264

Try this:

Public Function SolveEquation(ByVal eq As String) As Double
    Dim i_eq As Integer, y As Double
    ' eq : "67657657 = 18774x+82795"
    i_eq = InStr(1, eq, "=")
    y = 0#
    If i_eq > 0 Then
        y = Application.Evaluate(Trim(Left(eq, i_eq - 1)))
        'y =67657657
        eq = Trim(Mid(eq, i_eq + 1))
    End If

    Dim eq_1 As String, eq_2 As String
    Dim x_1 As Double, x_2 As Double
    Dim y_1 As Double, y_2 As Double
    x_1 = 0#: x_2 = 1#
    eq_1 = Replace(eq, "x", "*(" & CStr(x_1) & ")")
    y_1 = Application.Evaluate(eq_1)
    eq_2 = Replace(eq, "x", "*(" & CStr(x_2) & ")")
    y_2 = Application.Evaluate(eq_2)
    ' y_1 = 82795, y_2 = 101569

    ' Alternative solution from evaluation points
    ' SolveEquation = x_1 + (x_2-x_1)/(y_2-y_1)*(y-y_1)

    ' y = a*x+b
    Dim a As Double, b As Double
    a = (y_2 - y_1) / (x_2 - x_1)
    b = y_1 - a * x_1
    ' a= 18774, b = 82795

    ' Solution from linear form y = a*x+b
    SolveEquation  = (y - b) / a
    ' 3599.38542665388

End Function

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96771

In A1 place the String:

y = 18774x + 82795

In A2 place the y-value:

67657657

Then try this macro:

Sub Zolver()
    Dim sTr As String, X As Double
    Dim A As Double, B As Double, Y As Double
    sTr = Range("A1").Value
    sTr = Replace(sTr, " ", "")
    sTr = Replace(sTr, "x", "")
    sTr = Replace(sTr, "y=", "")
    sTr = Replace(sTr, "+", ",")
    A = CDbl(Split(sTr, ",")(0))
    B = CDbl(Split(sTr, ",")(1))
    Y = CDbl(Range("A2").Value)
    X = (Y - B) / A
    MsgBox X
End Sub

Upvotes: 1

Related Questions