Multiple goal seek

[Danielbuciak]

Hi all, I’ve made up a spread sheet in which I change a variable (depth in my case) and then use goal seek to obtain a value for theta for that corresponding depth then manually record the value obtained. Is there a way to record the value obtained for that variable then change the variable and record the corresponding value for. I tried recording a macro but struggled to record the values equal to the solved theta cell as it changes for new solution. For example for depth 1 goal seek found theta to be 15, then change to depth to 2 goal seek found theta equal to 20 and so on. But I can’t figure out how to leave depth one at the solution found for the corresponding theta cell.
Kind regards dan

 

[Col Delane]

Hi Dan & welcome to the Forum

I don't completely understand your requirements, but it looks to me like a little used Excel feature called a "Data Table" will provide your solution. (This functionality is found under the What-If button in the Forecast section of the Data tab on the Ribbon)

You can have one-way or two-way data tables, which will give you a calculated (rather than goal-sought) result for variations in either a single variable (one-way) or a combination of two input variables (two-way). In a nutshell, whenever the Data Table is recalculated, Excel effectively cycles through your application using in the linked cell (e.g. depth) each value of the variable listed in the Data Table and returns to the table the result of the output cell you're targeting (e.g. theta). It's an iterative calculation but does it all in one workbook calculation event - but it is NOT a backwards calculation like Goal-Seek (which tells you what input you need to get a desired calculated output).

Search the web for "Excel Data Tables" and check out how they work. (I think a one-way table will give you your answer.)

HTH