 SOLVED

New Contributor

Iterative calculations return #NUM

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

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?

Even my engineer programming teacher couldn't solve this one... HELP!!

7 Replies

Re: Iterative calculations return #NUM

@davididofff Other than a cool thing to try, what is the purpose of such a nonsense calculation?

Re: Iterative calculations return #NUM

Iterative calculations have many uses. As of doing it in excel, I agree and would prefer other apps. In this case, it is for an exam.

Re: Iterative calculations return #NUM

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.

Re: Iterative calculations return #NUM

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?

Re: Iterative calculations return #NUM

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.

best response confirmed by davididofff (New Contributor)
Solution

Re: 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.

Re: Iterative calculations return #NUM

Thanks! This works for me seems a bit complicated for such calculations but at least it works. Cheers!