excelsolverexcel-solver

Excel - Auto Increment Number until Finding Solution (Using No VBA and No Data Table)


I am proficient Excel User, but I do not want to use VBA or Data Table to solve the below problem.

Preferably, I want to use Goal Seek or Solver or any other simple method that does not require macros (loops) and not require large data table spanning many rows.

Here is the problem: In Cell B1, the Solution is 15,621 (there are other solutions over this number too)

However I want to start the search from 1, and adding 1 each time, or adding 5 each time (if possible, 1, 6, 11, etc.), until Cell D17 equals 1. It will eventually equal 1 say many times under a million.

I may want to change starting point from 20,000 onwards, etc.

Is this possible? Goal Seek doesn't find it as the formulas use Mod, Min, Max.. thus doesn't linearly converge.

Is there any other way to rewrite this problem that can be made of Goal Seek or Excel Solver.

This is a famous math problem (http://mathcentral.uregina.ca/QQ/database/QQ.02.06/meadow4.html), but I want to use Excel.

I know that I can solve using vba loops and data tables. But, I want a simpler solution that uses goal seek or solver or other unknown.

enter image description here


Solution

  • Excel's "Iterative Calculation" might do the trick. As seen here: ablebits.com: "Circular reference in Excel - how to check, find, enable, or remove"

    Iterative calculation allows circular dependencies of your formulae. In Excel 2010 you find it in "Excel Options" -> "Formulars" -> "Calculation options" -> "Enable iterative calculation". Note: 32767 is the maximum number of iterations in Excel 2010

    In your example you could have a variable in (let's say) B2 initially set to your minimum. B1 is then set to to be like B2 as long as the solution is not found. If the solution is found, it is set to B2 minus the increment. Now, if you change B2 to be B1 plus increment, the Excel's iterative calulation should stop at your solution.

    example:

    B2 =   1
    B1 =   =IF(D17<>1, B2, B2-1)
    B2 =   =B1+1
    

    Update (based on the comment by @ihightower):

    The iterative calculation stops when the values change less than the defined threshold "Maximum Change" (default: 0.001) or when the "Maximum Iterations" have been executed. At the end of each run, B2 already contains the initial value for the next one. To invoke the next iterative calculation, go to the result's cell D17, press [F2] and then [Ctrl]+[Enter].

    To jump to a new initial value, set B2 to the new minimum value and to =B1+1 afterwards.