Goalseek/Solver with calculation in limited range

**ajayb23** · 03-09-2013, 12:17 PM

I have a few calculation intensive spreadsheets, which take a long time to calculate completely. But all too often, I have to run goalseek/solver on a few lines, to quickly get a result.

However, every time I use goalseek/solver, the entire spreadsheet recalculates repeatedly (including areas which are not relevant to the particular solution).

Do we have a work-around this? Say I could define that goalseek should only re-calc rows 1 and 2, while solving for J2 equal to 0, by changing B2.

Thanks a lot!

**Alf** · 03-10-2013, 03:27 AM

If your calculation option is set to manual you could try something like

Please Login or Register  to view this content.

Alf

**shg** · 03-10-2013, 11:24 AM

@Alf,

That construct may point at different cells than those listed if the used range doesn't start in A1.

**MrShorty** · 03-10-2013, 01:12 PM

every time I use goalseek/solver, the entire spreadsheet recalculates repeatedly (including areas which are not relevant to the particular solution).

This doesn't seem consistent with my own use of Solver. Is it possible you have a lot of volatile functions (like INDIRECT and OFFSET) that calculate with each calculate event whether they need to or not? Or maybe with a large spreadsheet like that, you have too many dependencies for Excel to do a "smart" recalculation and so it does a full recalculation with each calculate event. http://www.decisionmodels.com/calcsecrets.htm

**ajayb23** · 03-10-2013, 01:16 PM

Thanks for the response. Unfortunately, when you 'goalseek' in VBA code, excel seems to be calculating rest of the sheet by default on each iteration. So setting calculation mode to manual, then calculating a targetted range does not work.