To find numeric solutions to formulas, it helps to put all the variables on one side of the equation.

Instead of A = (B-C)^D, use ((B-C)^D) - A = 0.

This algebra is best done by the user. VBA (or any programming language) doing algebra is nasty.

If you know 3 of the variables, this becomes a function of one variable. In your example it becomes (4800-750)^2 - A.

One way that I like to solve for zeros of an equation is to use the binary search method.

Note that the FindZeroBetween needs two values a and b where F(a) is positive and F(b) is negative.Code:Sub test() MsgBox FindZeroBetween(-50000000, 50000000) End Sub Function FindZeroBetween(startLow As Double, startHigh As Double, Optional Accuracy As Double = 1e-06) As Double Dim Low As Double, High As Double, Mid As Double Rem clean arguments Accuracy = Abs(Accuracy) If Accuracy = 0 Then Accuracy = 1e-06 If MyFunction(startLow) < MyFunction(startHigh) Then Low = startLow High = startHigh Else Low = startHigh High = startLow End If Rem check Select Case Sgn(MyFunction(Low) * MyFunction(High)) Case Is = 1 Rem both have same sign, aborth MsgBox "start values do not capture zero" Exit Function Case Is = 0 Rem one of the startvalues is a zero If MyFunction(Low) = 0 Then Mid = Low Else Mid = High End If MsgBox "MyFunction(" & Mid & ") = " & MyFunction(Mid) FindZeroBetween = Mid Exit Function Case Else Rem all is good End Select Rem binary search Do Mid = (High + Low) / 2 Select Case MyFunction(Mid) Case Is < (-1 * Accuracy) Rem mid becomes low Low = Mid Case Is > Accuracy Rem mid becomes high High = Mid Case Else Rem we have a solution Exit Do End Select Loop Until False MsgBox "MyFunction(" & Mid & ") = " & MyFunction(Mid) FindZeroBetween = Mid End Function Function MyFunction(x As Double) As Double MyFunction = ((4800 - 750) ^ 2) - x End Function

Note also that binary search is limited to "nice" functions. Continous is the primary requirement, there's a whold body of literature on the limits of binary search.

Note also that because of the way that computers store numbers, its best to look for ABS(F(x)) < delta rather then F(x)=0. Hence the Accuracy variable.

If the function is "nice" enough, Excel can search for and find suitable start points for FindZeroBetween.

Code:Sub test() MsgBox FindZero End Sub Function FindZero() As Double Dim EndPoint As Double EndPoint = 1 Do While True If Sgn(MyFunction(EndPoint) * MyFunction(-1 * EndPoint)) = -1 Then Rem good to go Exit Do Else EndPoint = 2 * EndPoint End If Loop FindZero = FindZeroBetween(EndPoint, -1 * EndPoint) End Function Function FindZeroBetween(startLow As Double, startHigh As Double, Optional Accuracy As Double = 1e-06) As Double Dim Low As Double, High As Double, Mid As Double Rem clean arguments Accuracy = Abs(Accuracy) If Accuracy = 0 Then Accuracy = 1e-06 If MyFunction(startLow) < MyFunction(startHigh) Then Low = startLow High = startHigh Else Low = startHigh High = startLow End If Rem check Select Case Sgn(MyFunction(Low) * MyFunction(High)) Case Is = 1 Rem both have same sign, aborth MsgBox "start values do not capture zero" Exit Function Case Is = 0 Rem one of the startvalues is a zero If MyFunction(Low) = 0 Then Mid = Low Else Mid = High End If MsgBox "MyFunction(" & Mid & ") = " & MyFunction(Mid) FindZeroBetween = Mid Exit Function Case Else Rem all is good End Select Rem binary search Do Mid = (High + Low) / 2 Select Case MyFunction(Mid) Case Is < (-1 * Accuracy) Rem mid becomes low Low = Mid Case Is > Accuracy Rem mid becomes high High = Mid Case Else Rem we have a solution Exit Do End Select Loop Until False MsgBox "MyFunction(" & Mid & ") = " & MyFunction(Mid) FindZeroBetween = Mid End Function Function MyFunction(x As Double) As Double MyFunction = ((4800 - 750) ^ 2) - x End Function

## Like this thread? Share it with others