Need to loop through a set of functions and access the changing variable in the functions

Hey, I've been having an issue. I need to set up a Newton Raphson method for approximating an answer to a complicated equation, line after line, for many conditions.

So for those who just want to skim this, my problem is that I need to set up a for loop inside of a sub that calls out functions inside said for loop, but I need the functions to have access to and change with the same changing variable set by the for loop.

Here's some context for the problem: I need to solve one cell based off of another similar to the way goalseek or solver would function, and loop down like 50 rows, referencing a table of values. The purpose of setting up this function using the Newton Raphson method has been that I am solving a complex math problem that has weird answers and I need to arrive at a real solution, and I need to do so more quickly than the solver or goalseek functions have been able to do for me. Which works! The newton raphson method is WAY faster for one line in my case, so that's not the issue.

So here's the part I need help with: I have one line/row of my spreadsheet completely solved and it solves the equation I need lightning fast. To do so, I have 2 functions set up.
My first function, f(x), is the equation I have simplified my problem to, and the second function, df(x), is its derivative. I use these to perform a taylor-series like iterative solution using a 3rd function, in which I call out those first two functions. Another subroutine calls for that 3rd function, and then outputs the value that the 3rd function spits out into a cell. All of this works phenomenally, for one line. My original plan was to solve for one line, and then put a big for loop around the whole procedure, referencing the changing variable in order to change the cells that the functions reference, and solve for many solutions in a vertical column that lines up with the rest of my data. Please let me know if that's not clear enough, I'll be happy to elaborate.

Setting up a for loop inside of each cell obviously won't work, and the variable I loop through, "j", can't be referenced within the other functions if I call it out within the sub, where it needs to be. I want to call out, for example,
b = Cells(j, "CJ") within the function that the sub calls for. I want to have the sub be a big for loop that changes j... and reference j within the functions as it changes.

I HAVE GOT AROUND my problem by making a for loop that inside my sub fills up a column in the spreadsheet with 1's, and in my functions I set j equal to a command that counts the number of cells filled in the column range, but since it has to output cells and count rows it takes too long for the size of data I need it for (like 50 lines.) Is there ANY way to reference a variable, WITHIN A CHANGING FOR LOOP, inside of a function that you call out? Will global variables work? (I've tried global variables but can't set one up that changes where I need it to.) PLEASE help me if you can, I'm going insane trying to find an answer, all I've found on most threads is basic for loop help and referencing things within subs, but nothing that quite helps with my super specific problem. Any help would be appreciated, this is my first time posting in a thread so any thread advice would also be appreciated. Thanks in advance!

I've provided a workbook set up the same way as my code. Thanks again, please let me know if I can clarify further!

It is not clear to me exactly how you want to program this.

As I noted in the other thread, I would be inclined to do this using circular references with no VBA.

Using this as a starting point, I would probably approach this differently. I would structure Function NR(...) so that it receives the parameters for the equation, then enter NR() into the spreadsheet. With the right relative and absolute references, I could then copy this down. If I followed the logic correctly (note that I have completely left out public sub Newton-raphson()):

Please Login or Register  to view this content.

I then entered =NR(B4,A4,C4,0) into G4 and it returned the same value as you show in D4. When it brought up debug mode at the stop statement, I followed it through and it seemed to be working. I commented out the stop statement, then copied G4 down to G37 and got a list of results. They kind of matched up with what is in column D, but not exactly, so I am not sure I am understanding exactly what results you expect. Even so, it illustrates the strategy I would use (if I did not use the circular reference thing) -- Build a good UDF that will find the desired root at a give b, c, and d. Then enter that UDF into the desired output cell and copy the formula down.

Is that helpful at all, or do you have a strong preference for doing the "rows" loop in VBA?

1st change: edit F2 to be =IFERROR(I2,E2) to get out of the div/0 error
then I noticed that it was not changing x very much per loop, even though it seemed to be a long from y=0. Carefully reviewed the formulas in G2 and H2 to make sure they looked right (comparing them to the formulas you used in the f() and df() VBA functions from the previous file). I noticed that the df() VBA formula had a plus sign before the last term of the dy/dx formula, but your formula in H2 for dy/dx had a multiplication (*) symbol. Edited the formula in H2 to replace this * with + (...+D2*B2^2 instead of ...*D2*B2^2), and the problem seems to converge readily on 167.3646...

Could that be the problem?

It is difficult to make specific recommendations without understanding the details of your problem. Selecting the initial guess is an important part of a NR (or any other root finding) algorithm. Without knowing the specifics of this situation, it is difficult to make a suggestion.

I notice from you previous examples that you seem to be looking for the "middle" root. Your function seems to yield a fairly standard cubic function with three roots, a left root, a right root, and a middle root. All of your previous examples seem to work just find using 155 as the initial guess (or something similar in that range). I would guess that the current set of parameters either has a very different shape to the curve, or the location of the middle or the left root is dramatically different from the previous examples. Things I would try looking at:

1) If you looked at my NR tutorial spreadsheet, you should have noticed that I included a section outside of the root finding part that plots the current equation on a scatter chart. I will usually do this for the "difficult" problems to better visualize what is going on. If your current issue is still a cubic polynomial, I would expect a plot of the function to help you see why it is converging on -392 and not moving towards the 1000ish value.
2) Assuming the problem still has the same basic shape and you are still looking for the middle root, I would expect that a suitable initial guess could be found by determining the x values for the local max and local min that is present in these cubic functions. Since the first derivative is a quadratic, you should be able to find these two points fairly easily using the quadratic formula.
3) It could be valuable to locate the local max and min because it is very possible for the NR algorithm to "switch" roots if it gets too close to these regions where the slope is "small".
4) All of that, without neglecting the possibility of a typo in any of the formulas (like we saw in posts 4 and 5).

Without specifics, I don't think I can help more than that, but that should also give you some ideas for analysis and debugging.