0

I got this equation to solve in VBA:

Energy = y + (0.5) ^ 2 / ( 2 * 9.81 * y ^ 2 )

but I got the Energy value and want to solve for the y value.

In R is easy: uniroot. There is something like this?

GSerg
  • 76,472
  • 17
  • 159
  • 346
Bryan Souza
  • 497
  • 2
  • 10
  • nop :/ maybe a i need an numerical method – Bryan Souza Jan 20 '21 at 13:33
  • There isn't anything in VBA for solving equations. There is Solver in Excel though, you can set up a model for that equation. Or you can try to [express `y` in terms of `Energy`](https://www.wolframalpha.com/input/?i=x+%3D+y+%2B+%280.5%29+%5E+2+%2F+%28+2+*+9.81+*+y+%5E+2+%29%2C+solve+for+y)... – GSerg Jan 20 '21 at 13:51
  • What kind of energy do you calculate using that formula? – FaneDuru Jan 20 '21 at 14:16

3 Answers3

2

The equation is a cubic polynomial with two positive roots and one negative. We can eliminate the negative one, and consider a direct evaluation of the two positive roots with the following VBA code in a Module.

Global Const g As Double = 9.81
Global Const PI As Double = 3.14159265358979

Public Function CalcE(ByVal y As Double) As Double
    CalcE = y + (0.5) ^ 2 / (2# * g * y ^ 2)
End Function

Public Function SolveForY(ByVal E As Double, Optional ByVal second As Boolean = False) As Double
    ' Solve "E = y + (0.5) ^ 2 / ( 2 * g * y ^ 2 )" for "y"
    If Not second Then
        SolveForY = E / 3# - 2# * E * Sin(Asin((16# * E ^ 3# * g - 27#) / (16# * E ^ 3# * g)) / 3#) / 3#
    Else
        SolveForY = E / 3# + 2# * E * Sin(Asin((16# * E ^ 3 * g - 27#) / (16# * E ^ 3 * g)) / 3# + PI / 3#) / 3#
    End If
End Function

and the supporting Asin() function defined in terms of Atn().

Public Function Asin(ByVal x) As Double
    If Abs(x) < 1# Then
        Asin = Atn(x / Sqr(1# - x * x))
    Else
        Asin = 2 * Atn(1) * Sgn(x)
    End If
End Function

Some example results are

? SolveForY(1#)
0.12035578724076 

? SolveForY(1#, True)
0.986917853921696

And checking the solutions

? CalcE(0.12035578724076)
 0.999999999999997 

? CalcE(0.986917853921696)
 1
John Alexiou
  • 28,472
  • 11
  • 77
  • 133
1

Consider using algebra:

Energy = y  +  (0.5)^2  /  ( 2 * 9.81 * y^2 )
Energy = y  + .25  /  ( 19.62 * y^2 )
Energy  *  ( 19.62  *  y^2 ) = y  *  ( 19.62  *  y^2 )  +  .25
Energy  *  19.62  *  y^2  = 19.62  *  y^3  +  .25
0  = 19.62 *  y^3   -  Energy * 19.62  *  y^2  +  .25

and apply standard techniques:

Previous Post

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
1

Here's a translation of John Alexiou's answer from VBA to Excel formulas. If energy in in cell B2 then the three solutions are given by:

=B2/3-2*B2*SIN((ASIN((16*B2^3*9.81-27)/(16*B2^3*9.81))+0*PI())/3)/3
=B2/3-2*B2*SIN((ASIN((16*B2^3*9.81-27)/(16*B2^3*9.81))+4*PI())/3)/3
=B2/3-2*B2*SIN((ASIN((16*B2^3*9.81-27)/(16*B2^3*9.81))+2*PI())/3)/3

The third equation gives negative y.

xidgel
  • 3,085
  • 2
  • 13
  • 22