Goal seek with intermediate iterations

[kkjensen]

Howdy all!

I've got a problem that requires tweaking one input then running a pile of iterations between tweaks to the input. Basically a goal seek with ten or twenty thousand iterations per cycle....imagine sitting a through a whole tv episode with your finger on f9

Check if the converging value is headed in the write direction, tweak the input a bit more and repeat.

I'm imagining a VBA script to shut off screen updates, fire off the 20k interactions, or perhaps until the running average of the last hundred values flatlines close enough to a particular value, see if that value is too high or low, adjust the input appropriately and repeat

Anyway, it's been a while since I've done a deep dive on anything or written VBA so I though I'd ask for some help to get started with a version of Excel that about 15yrs newer than the last time I had to do something a little challenging.

 

[JvdV]

Do you have a small set of sample data to show what it is you are trying along with some expected results? It sounds like you could dive into the newer lambda functionality if you talk about iterations. That would avoid the need for VBA alltogether perhaps.

 

[kkjensen]

Do you have a small set of sample data to show what it is you are trying along with some expected results? It sounds like you could dive into the newer lambda functionality if you talk about iterations. That would avoid the need for VBA alltogether perhaps.

I do like the sound of not needing to use VBA.

Unfortunate it's a very large template with multiple large tables of data full of cells that all average off one another (literally each cell is the average of what is above, below, left and right) so messing with it too much gives the possibility of circular references so I'm not too sure how to take a snippet of what is there. Overall the math is simple but the iteration-required structure of the whole thing unfortunately requires someone to literally hold f9 for a while and then tweak a single input manually before starting over. I see the possibility of automating it

Wat is the labda functionality about? I'll do some looking myself. I've been thinking about putting together an equation that takes a bunch of these averages and checks them against each other and puts an overall change into one cell so the goal seek function could be used but the scalable nature of these tables makes it a bit tough to decide which cells to use which is why I was looking at a brute force force method to just iterate into submission (where values should converge)