0

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

ihightower
  • 3,093
  • 6
  • 34
  • 49
  • How did you try Solver? (Have you made an attempt yet?) – BigBen Jun 17 '20 at 16:46
  • @BigBen hi yes i did try. set objective d17=1 by changing cells b1. with min =1 and max =1. something like that. as i think, there is no set up in solver to automatically march from 1 to say 100,000 and stop as soon as it reached the objective. at least i am not aware. if there is something like this, this will be solved. but as far as i know.. nt possible. i have solved using vba as well as using data model (both are easy to do with above model setup). i do wish, there is a simpler non vba and non large formula or datatable requirement. the formula has mod, int, min, max, thus no convergence – ihightower Jun 17 '20 at 18:30
  • hi anyone have a solution. can't solver be made to simply increment number until it finds true in the objective cell and stop there. – ihightower Jul 01 '20 at 05:43

1 Answers1

1

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.

Volker
  • 76
  • 4
  • yes thank you it works!!! excellent!. i thought about iterative calculation but couldn't have the imagination to set it up this way. i will mark your answer soon and i will share the file also. could you think about how we can find subsequent answers after the 15,621 by changing what. and to trick if 32767 iteration by adding some numbers, etc. i want a good understanding to have a generic understandable solution. in any case, your answer works for the first 15,621 in a flash!. 31246 and 46871, 62496,...., 109371 and onwards.. or next solutions. for example how to find 109371 (number >100k) – ihightower Jul 11 '20 at 13:35
  • i found a workaround by entering cell D17 (press F2 to do an edit), then just press enter. this makes the iteration run again.. and it finds the next answer. and doing it again. it finds the next answer and so on. it keeps on finding the next answer by doing F2 and Enter in Cell D17 (Check Cell). I can just get the idea why that works, but still not sunk in. I hope you have a good input. and i may edit the answer slightly.. and mark your answer. thank you so much. shortcut... F2 then Ctrl+Enter then F2 then Ctrl+Enter and so on. – ihightower Jul 11 '20 at 13:58
  • You're right - good idea. When finding a solution, then B2 is already set to the initial value of the next iteration. Hence F2 then [Ctrl]+[Enter] works here. I'll add this to my answer (up there). – Volker Jul 13 '20 at 07:32