Hi,
I have a problem with trying to get VBA to iterate a solution for me.
Basically, I have an initial guess value which is then used to calculate something else and then that is used to calculate the answer. This answer is then used to replace the initial guess value. How do I do this in VBA for different columns and rows?
J (initial guess) > formula (fn(J))>J (answer)=J(inital guess) and so on...
Can someone help?
Hi Hassanm and welcome the forum.
Please be more specific in what you want. Maybe posting the workbook and captions will help.
Thanks,
exacty as you wrote above
so if your initial guess is in a1 and formula in B1 you can try:
this way you do 100 iterations.
Please Login or Register to view this content.
Best Regards,
Kaper
As you can see I have done the VBA code you mentioned Kaper.
amartinez988. You can see what I need in more detail. The initial guess is used to feed into the column on the right (C3). C3 is a function of D3. D3 is a function of E3 and finally F3 is a function of B3.
I need to solve this so that B3 and F3 match. And I also need to do this for multiple rows and columns.
I don't quite understand the formulas that you are putting but the complete formula to replace that in only one instead of using cell by cell is:
Formula:
Please Login or Register to view this content.
Let us know
Hi hassanm,
To make it that way, your function shall be convergent (hope it's the right term - never had math classes in English).
And the one you have shown do not look to be convergent, because your answer is =3,75*(1-initial_guess)
Not getting deeper into math you get: positive - negative - positive - negative .... sequence, while absolute value will be bigger and bigger every step.
Yes they could be replaced with one long equation but I need it cell by cell as they are variables I need to show.Originally Posted by amartinez988
I don't quite understand the formulas that you are putting but the complete formula to replace that in only one instead of using cell by cell is:
Formula:Please Login or Register to view this content.
Let us know
How do I create a macro to allow me to iterate B3=F3 over different rows and columns?
If you need just B3=F3 then simply solve the equation. B3 (=F3) will be just 15/19 (0.78947368....)
PS. see also my second post above (you may not noticed it).
So you want to substitute the value in column B by the one obtained on column F? The first value in B always going to be replaced by the last obtained in F?
I have produced a sample excel workbook to hopefully provide a better idea of what I need. You can see that equation 8 is the answer whilst the initial guesses is used to calculate equation 2 which leads to an answer given in equation 8.
So D7 (initial guess) gives an answer of provided in cell D112. I would a VBA code that will be able to iterate this to give me a solution where D7 and B112 match. And apply it to the rest of the cells (column D:N).
In my opinion with this approach, there is no chance to solve it, because you in 4 of your equations use random numbers generator.
So each recalculation will change the result.
Moreover again absolute base maths seems to be a problem.
Your equation 8 (D112) is:
Formula:Please Login or Register to view this content.
while equation 7 (D99) was:
Formula:Please Login or Register to view this content.
equation 6 -> D86 (one of random values! is used only in 2 above equations). So just write in D112:
Formula:Please Login or Register to view this content.
and you can delete rows 85 to 110.
the same step by step up, and finally you have only
1) your constants in column B (by the way, have you noted that you never use const C - cell B5? - well this case it does not matter because the whole appoach is wrong.)
2) your initial values (you used all the same)
3) just one equation:
Formula:Please Login or Register to view this content.
if not the last part - could be easily solved, but as with every calculation the jast part changes randomly:
no way.
Side comment:
this last part:
Formula:Please Login or Register to view this content.
can be pretty well fitted with simple normal (gaussian) dustribution of standard deviation 0.5 and expected value of -0.5.
Of course with real gaussian one can go below -2 or above 1 (which are limits for 2 negative random and one positive).
so if one do not mind thin tails going out of (-2;1) range, such approach could be used:
Formula:Please Login or Register to view this content.
Last edited by Kaper; 07-08-2015 at 10:40 AM.
I have tried to use this iteration but I do not know how to apply it to different cells. I have a range of cells. Range (BP393:DX432) equate to (F3:BM42).exacty as you wrote above
so if your initial guess is in a1 and formula in B1 you can try:
this way you do 100 iterations.Please Login or Register to view this content.
I have done the first one where I match BP393 to F3 as shown below and I have attempted to do it for the range. This does not work?
Please Login or Register to view this content.
if F3:... are initial values.Please Login or Register to view this content.
But please read through my comments above.
Math is not only to make students life harsh.
It is painful torture tool for those who try to calculate something not following math rules.
The code works perfectly for the first column i.e. iterating BP393:432 to match F3:F42 but it does not seem to work for the remaining columns.
The code is this atm:
BP393:DX432 being the initial guessPlease Login or Register to view this content.
note that your ranges have different numbers of columns - 61 and 60 resp.
correct it first.Please Login or Register to view this content.
I have corrected to :
Am I right in suggesting that this piece of code will iterate 100 times and find a solution whereby BQ393 = G3, BR393 = H3 and so on across the whole row as well as find the solution down the column i.e. BQ393 = G3, BQ394 = G4 and so on.Please Login or Register to view this content.
yes. 100 times.
Many thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks