Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to indicate that UDF didn't converge?
I have a handful of UDF's that use iterative algorithms to numericall arrive at a solution (usually either a Newton-Raphson type algorithm o successive approximations). I like to put a check on the number o iterations, to prevent getting stuck in an infinite loop should th problem diverge. Generic function looks something like: function myudf(arglist, optional init) if ismissing(init) then init= some reasonable initial guess for generi problems of this sort code setting up initial values for the iteration it=0 do it=it+1 iteration code to find next value from old value loop until convergeance criteria or it=100 myudf=result end function What I would like to add is something to let me know when th convergeance criteria hasn't been met, but rather the function ha ended because we've reached the maximum number of iterations. I'm jus looking for ideas. How would you inform me that the function reache maxiterations without converging? message box? have the functio return an error? other possibility? Thanks in advance -- MrShort ----------------------------------------------------------------------- MrShorty's Profile: http://www.excelforum.com/member.php...fo&userid=2218 View this thread: http://www.excelforum.com/showthread.php?threadid=49559 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to indicate that UDF didn't converge?
Is the result of the function being used in other formulae?
You could always just return "Not converged!" (or other appropriate message) and have any downstream formulas only handle a numeric return value. Add some conditional formatting to the cells to highlight non-numeric results. Tim. -- Tim Williams Palo Alto, CA "MrShorty" wrote in message ... I have a handful of UDF's that use iterative algorithms to numerically arrive at a solution (usually either a Newton-Raphson type algorithm or successive approximations). I like to put a check on the number of iterations, to prevent getting stuck in an infinite loop should the problem diverge. Generic function looks something like: function myudf(arglist, optional init) if ismissing(init) then init= some reasonable initial guess for generic problems of this sort code setting up initial values for the iteration it=0 do it=it+1 iteration code to find next value from old value loop until convergeance criteria or it=100 myudf=result end function What I would like to add is something to let me know when the convergeance criteria hasn't been met, but rather the function has ended because we've reached the maximum number of iterations. I'm just looking for ideas. How would you inform me that the function reached maxiterations without converging? message box? have the function return an error? other possibility? Thanks in advance. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=495594 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to indicate that UDF didn't converge?
In part it depends on what comes next; i.e. what does your code or the user
do with the result. The test for non-convergence should be obvious: do it=it+1 iteration code to find next value from old value loop until convergeance criteria or it=100 If it=100 Then ' non-convergence result Else ' regular result End If end function The message box works if what you need to do is to alert the user; but not so good if you need your code to respond to the error result. If you need the code to respond, there are 2 basic things you can do: 1) If possible, and it may not be, have a distinctive return value that flags the result of the function as meaning "did not converge." For example, if the normal function would ALWAYS return a positive value, you could use -1 as a flag value to show it had not converged: If it=100 Then myudf = -1 Else myudf=result End If end function This avoids raising an error, but it can only be used for specific circumstances and the person writing the code must know the return value that flags it as not having convereged. Probably the most rigorous, general use solution is to use an error. Two different approaches are possible: Raise an error with Err.Raise vbObjectError+something and then use error trapping to deal with error results (test the error number to find if it was due to non-conveergence); or set the function return value to a variant and use myudf=CvErr() to return a particular error type, which could be tested with The IsError() function. This approach has the advantage of working well as an Excel user function on a worksheet, since it will lead to the #ERR! result and not bring anything to a crashing halt. I won't try to decide for you; but my general thought is: Need user to respond or be alerted: use messagebox Wish for code to handle without dealing with errors: use flag value, if possible Flag value not possible, or you want error to be raised, especially for worksheet function: raise an error or return an error value. HTH! -- - K Dales "MrShorty" wrote: I have a handful of UDF's that use iterative algorithms to numerically arrive at a solution (usually either a Newton-Raphson type algorithm or successive approximations). I like to put a check on the number of iterations, to prevent getting stuck in an infinite loop should the problem diverge. Generic function looks something like: function myudf(arglist, optional init) if ismissing(init) then init= some reasonable initial guess for generic problems of this sort code setting up initial values for the iteration it=0 do it=it+1 iteration code to find next value from old value loop until convergeance criteria or it=100 myudf=result end function What I would like to add is something to let me know when the convergeance criteria hasn't been met, but rather the function has ended because we've reached the maximum number of iterations. I'm just looking for ideas. How would you inform me that the function reached maxiterations without converging? message box? have the function return an error? other possibility? Thanks in advance. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=495594 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to indicate that UDF didn't converge?
Thanks for the responses. In response to, "Is the result of the function being used in other formulae?" Not always, but often yes, the result is used in further downstream calculations. Of course, if I haven't acheived the desired accuracy in this calculation, or it has diverged to some "garbage" answer, then all downstream calculations aren't as accurate as desired or are "garbage" as well. I don't know if I'm too worried about the effect of an error value (whether xlerrorvalue or string message) on any downstream calculations. My intent here is to alert the user (namely myself, as I'm the only intended user) that "this value didn't converge." I generally don't like to have a computer do too much of my thinking for me, lest the computer really foul things up. I simply want to be notified that a specific calculation didn't converge so I can go in and determine (using the computer in my head) if the calculation simply needs a few more iterations, or is diverging, or is caught in an oscillation, or what. Then I can decide how best to respond based on my knowledge of the numerical algorithm and the specifics of the calculation at hand. I'm not sure I like the idea of a messagebox in a UDF. If I have 20 incidents of the UDF in a spreadsheet, then I potentially could get 20 successive messageboxes to clear with each calculation event. I would also have to figure out (not sure that it would be difficult) how to identify within the message box which incident of the function isn't converging. A message box would have the advantage of allowing for more flexible alert options. I could list the oldresult, newresult values and any other variable values that could help in troubleshooting just what is going wrong. One solution I've thought of: have the UDF return two values. Something like: myudf=array(result,it). Then I could scan down the second column of return values for (or use conditional formatting to highlight) cases where it=100. I could also see if the function was returning a potentially reasonable answer (ie maybe it just needs a few more iterations) or if it was diverging to garbage. Thanks for the responses. It helps me think through what I'm trying to do. Any other ideas, or comments on the discussion so far, I'm still thinking this through. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=495594 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converge tabs | Excel Discussion (Misc queries) |