Nov 06 2021 11:42 AM - edited Nov 06 2021 12:00 PM
Hi!
I am running a T420 (old) lenovo thinkpad laptop with windows 10 pro and excel - office 365.
When trying to use iterative calculation, excel only returns #NUM, even for simple calculations.
For example :
A1=A2^3(should be 8)8)
A2=10-A1(should be 2)
Iterative calculation is enabled and I tried different number of iterations and precision, but it never works.
Here are different fixes I tried to no avail:
Tried installing an older version of excel (2010) but had same problem
Tried repairing office through office app
Does anyone have any clue what could be happening?
Thanks in advance
Even my engineer programming teacher couldn't solve this one... HELP!!
Nov 06 2021 12:05 PM
@davididofff Other than a cool thing to try, what is the purpose of such a nonsense calculation?
Nov 06 2021 12:09 PM
Nov 06 2021 01:16 PM - edited Nov 06 2021 01:16 PM
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.
Nov 06 2021 01:52 PM
Nov 06 2021 03:39 PM
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.
Nov 06 2021 08:06 PM - edited Nov 06 2021 08:17 PM
Solution@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.
Nov 08 2021 09:18 AM
Nov 06 2021 08:06 PM - edited Nov 06 2021 08:17 PM
Solution@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.