UDF to solve or invert formula

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
Hello,

I have a simple formula, p = (a+b)^n - b^n, but need to find n in terms of a, b and p. Problem is there isn't an exact algebraic solution (at least not according to WolframAlpha), so I need to create a fully automatic user defined function (UDF) that effectively acts a bit like goal seeker or solver, but without using a macro (Sub) as that has disadvantages like changing cells and blocking undo, etc.

Is there a generic UDF to solve or "invert" any given formula?

This would be such an incredibly useful function, so I can't imagine it hasn't been done, but all I can find are goal seek macros, which is not what I want.

Thanks,
Kelvin
 
Last edited:

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
PS., I guess the syntax of the UDF should be something like:

SOLVE(Input formula f(x), Target value for f(x), [Initial estimate for x (optional)], [Min x (optional)], [Max x (optional)]) and the output value of the function would be x.

Would that be possible?
 
Last edited:

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
So I created the SOLVE function (code below), and it seems to work great in some cases:

SOLVE("x^2",2) = 1.4142135623731

But not in other cases, for example:

SOLVE("x^2",2,1,0,5) = Error: 1.50993850552705E-03
SOLVE("LN(x)",2) = #VALUE!

Any idea why it's not working reliably?

Code:
Function SOLVE(Fx, Target, Optional x0 = 0, Optional xMin = -10 ^ 16, Optional xMax = 10 ^ 16, Optional yTol = 10 ^ -12, Optional iMax = 200)

    For i = 1 To iMax
        x1 = x0 + (xMax - x0) * 0.5 ^ i
        x2 = x0 - (x0 - xMin) * 0.5 ^ i
        y0 = Abs(Evaluate(Replace(Fx, "x", x0)) - Target)
        y1 = Abs(Evaluate(Replace(Fx, "x", x1)) - Target)
        y2 = Abs(Evaluate(Replace(Fx, "x", x2)) - Target)
        If y1 < y0 And y1 <= y2 Then
            x0 = x1
            y0 = y1
        ElseIf y2 < y0 And y2 <= y1 Then
            x0 = x2
            y0 = y2
        End If
        If y0 = 0 Then Exit For
    Next i
    
    If y0 < yTol Then
        SOLVE = x0
    Else
        SOLVE = "Error: " & y0
    End If

End Function
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,959
Without delving too deeply into your code, it appears that you are just doing a binary search within the range. Which is reasonable enough with most functions. But if the function is discontinuous, like LN, it appears that you're evaluating it at 0, which would cause a problem.

You really should test the yTol within the loop too.

And if you want to learn a bit more, and find more efficient algorithms, try searching for Newton's Method.

Good luck!
 

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
Thanks Eric.

Does anyone have any ready made code for the Newton method so that I can integrate and compare, if it's really more efficient?

Thanks!
 

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
So I managed to improve with Newton's method, and now it's working perfectly. :)

This short user defined function can solve pretty much any formula, exactly what I wanted. It usually converges within just 4 to 6 cycles (though I allow up to 20 just in case).

Code:
Function SOLVE(Fx, Target, Optional x0 = 0.5, Optional yTol = 10 ^ -12, Optional iMax = 20)

    dx = 10 ^ -6
    
    For i = 1 To iMax
        x1 = x0 + dx
        y0 = Evaluate(Replace(Fx, "x", x0))
        y1 = Evaluate(Replace(Fx, "x", x1))
        x0 = x0 - (y0 - Target) * dx / (y1 - y0)
        y0 = Evaluate(Replace(Fx, "x", x0))
        dy = Abs(y0 - Target)
        If dy < yTol * Target Then Exit For
    Next i
    
    If dy < yTol * Target Then
        SOLVE = x0
    Else
        SOLVE = "Error: " & dy
    End If

End Function
Thanks for all the help. :-/
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,959
Just a couple of pointers, it's good to see that you figured out a nice function.
 

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
Thanks again.

I always prefer to calculate exact roots with algebra where possible as it's more efficient, precise and reliable, but so many times I haven't been able to so I'm really pleased to have this as back-up. :)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,959
Yes, it is nice when you can solve an equation exactly. But in the real world, there are SO many situations where you can't. If you get into differential equations or numerical analysis, you'll see lots of examples of that. In such a case, it's nice to have some way to solve the equation. It's a matter of using the right tool for the right job. ;)
 

Forum statistics

Threads
1,081,527
Messages
5,359,295
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top