Forum Discussion
Iterative calculations return #NUM
davididofff .... In English, the problem is: ``find a number such that 10 minus the number cubed is the number``.
Try the following system of formulas:
A1: =IF(A3="",0,IF(A2=0,A1,A1+1))
A2: =10-A1^3-A1
A3: empty, initially
Alternatively:
A1: =IF(A3="",0,IF(A2=A1,A1,A1+1))
A2: =10-A1^3
Enter anything into A3 to start the iterative calculation. Delete the value in A3 to start over.
For grins, change 10 in A2 to 2, 30, 68, 130, etc.
Remember to delete the value in A3 before changing A2. Otherwise, we get the wrong result because A1 does not start at zero.
That's not the solver (which exists as add-in for Excel), that's literally iterations. Excel starts from blank cells which is zero. If only one iteration
A1=0^3=0
A2=10-0=10
Next (2nd iteration)
A1=10^3=1000
A2=10-1000=-990
Next (3rd iteration)
A1=-990^3=-970299000
A2=10-(-970299000) = 970299010
If you keep default 100 iteration very fast you'll be out of limits for number, thus #NUM! error. I guess less than 10 iterations enough.
- Thanks for your answer! I put 1 ,2,3... number of iterations and indeed you are correct. Those are the iterations excel proceeds to. Isn't excel supposed to converge towards the answers of 2 and 8?
You may Use Goal Seek to find the result you want by adjusting an input value (microsoft.com) combining your two formulas into one, e.g. =10 - a2 - a2^3 finding with Goal Seek value in A2 which gives zero result for the formula (you may place it in any other cell). In A1 simply =a2^3.