Anyone please help me.....I want to compare the results of one iteration to its previous iteration and update the lowest of the values in a new cell. The new cell values should always show the lowest value of all iterations. How could I do that?
Anyone please help me.....I want to compare the results of one iteration to its previous iteration and update the lowest of the values in a new cell. The new cell values should always show the lowest value of all iterations. How could I do that?
you should be a little bit more specific of what you mean by iteration and how your sheet is set up.
Thank you very much for the reply... I will explain the problem. The values of the cells are linked formulas based on random numbers and it will be updated and recalculated to a new value by using the F9 tab. Once the value of the cell got updated, I am unable to find or save the previous value and I want to compare the updated value of a particular cell to that of the previous value and I want to obtain the lowest of values.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
Never use Merged Cells in Excel
Thank you very much for your kind help
The values shown in yellow highlights starting from Cell B 249 are the values obtained using a set of formulas calculated above. Each time the values are recalculated using the F9 tab, the chk fn will get updated. The values of chk fn in the cell B 249 needs to be compared with the next obtained value after recalculation and if the chk fn value is less, it should be updated in cell B264 , otherwise retain the values. The values shown in blue highlights starting from B 264 should represent the values corresponding to the chk fn. So that the lowest value for chk fn should be obtained after several recalculations in cell B264 and the corresponding values in the blue highlighted area. The problem is shown in the attached screenshot. Please help me in this regard.
Ok thats already a good base but i am not sure if i understand the need so let me try to summarize it my way.
1. You Hit F9 and Recalculation happens
2. You want to Check if the Yellow FN is lower than the Blue FN
3. if yellow is lower than blue all the yellow values are stored in the blue area
4. if yellow is bigger than blue blue stays untouched
This way blue always holds the optimal parameters
Is that correct so far?
Edit: I made a sheet based on my assumptions so if i am right you already have something to work with.
Some information about it.
1. i set the Calculation mode of the Sheet to Manual so Calculation only happens if you press F9 this way it will not always be triggered when you change a cell.
2. I see in Column AF you have some Circula Reference Formula to count itereations that should be removed ( i did not remove it)
3. I added a counter for iterations in B252 which adds 1 every time calculation happens
4. i wrote a small script that is linked with the button "auto Iterate" it will ask you how many iterations you want to be done then calculate the sheet that many times
when it is finished it tells you in a message box how often the blue fn changed while doing the iterations and at wich iteration it changed the last time so if you do 1000 iterations for example it says
"Minfn changed 20 times in 1000 Iterations and the last change happend in Iteration 459"
Thats about it hope i understood your request correctly :D
edit2: you can remove the last msgbox from the code used it to check how long it runs with and without screen update :D
Last edited by LordLoki; 04-07-2016 at 04:36 AM.
Exactly the same as you mentioned is needed for calculation. Thank you so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks