Home |
Search |
Today's Posts |
#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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converge tabs | Excel Discussion (Misc queries) |