Discussion Re: Iterative calculations return #NUM in Excel
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934677#M121258
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?Sat, 06 Nov 2021 20:52:30 GMTdavididofff2021-11-06T20:52:30ZIterative calculations return #NUM
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934477#M121247
<P>Hi!</P><P>I am running a T420 (old) lenovo thinkpad laptop with windows 10 pro and excel - office 365.</P><P>When trying to use iterative calculation, excel only returns #NUM, even for simple calculations.</P><P>For example : </P><P>A1=A2^3(should be <LI-EMOJI id="lia_smiling-face-with-sunglasses" title=":smiling_face_with_sunglasses:"></LI-EMOJI><LI-EMOJI id="lia_smiling-face-with-sunglasses" title=":smiling_face_with_sunglasses:"></LI-EMOJI></P><P>A2=10-A1(should be 2)</P><P>Iterative calculation is enabled and I tried different number of iterations and precision, but it never works.</P><P>Here are different fixes I tried to no avail:</P><P>Tried installing an older version of excel (2010) but had same problem</P><P>Tried repairing office through office app</P><P>Does anyone have any clue what could be happening?</P><P>Thanks in advance</P><P>Even my engineer programming teacher couldn't solve this one... HELP!!</P><P> </P><P> </P>Sat, 06 Nov 2021 19:00:06 GMThttps://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934477#M121247davididofff2021-11-06T19:00:06ZRe: Iterative calculations return #NUM
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934536#M121248
<P><LI-USER uid="1209070"></LI-USER> Other than a cool thing to try, what is the purpose of such a nonsense calculation?</P>Sat, 06 Nov 2021 19:05:40 GMThttps://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934536#M121248Riny_van_Eekelen2021-11-06T19:05:40ZRe: Iterative calculations return #NUM
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934537#M121249
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.Sat, 06 Nov 2021 19:09:10 GMThttps://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934537#M121249davididofff2021-11-06T19:09:10ZRe: Iterative calculations return #NUM
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934630#M121253
<P><LI-USER uid="1209070"></LI-USER> </P>
<P>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</P>
<P>A1=0^3=0</P>
<P>A2=10-0=10</P>
<P> </P>
<P>Next (2nd iteration)</P>
<P>A1=10^3=1000</P>
<P>A2=10-1000=-990</P>
<P> </P>
<P>Next (3rd iteration)</P>
<P>A1=-990^3=-970299000</P>
<P>A2=10-(-970299000) = 970299010</P>
<P> </P>
<P>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.</P>Sat, 06 Nov 2021 20:16:59 GMThttps://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934630#M121253Sergei Baklan2021-11-06T20:16:59ZRe: Iterative calculations return #NUM
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934677#M121258
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?Sat, 06 Nov 2021 20:52:30 GMThttps://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934677#M121258davididofff2021-11-06T20:52:30ZRe: Iterative calculations return #NUM
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934842#M121267
<P><LI-USER uid="1209070"></LI-USER> </P>
<P>You may <A href="https://support.microsoft.com/en-us/office/use-goal-seek-to-find-the-result-you-want-by-adjusting-an-input-value-320cb99e-f4a4-417f-b1c3-4f369d6e66c7" target="_blank">Use Goal Seek to find the result you want by adjusting an input value (microsoft.com)</A> 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.</P>Sat, 06 Nov 2021 22:39:16 GMThttps://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2934842#M121267Sergei Baklan2021-11-06T22:39:16ZRe: Iterative calculations return #NUM
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2935056#M121278
<P><LI-USER uid="1209070"></LI-USER> .... In English, the problem is: ``find a number such that 10 minus the number cubed is the number``.</P><P> </P><P>Try the following system of formulas:</P><P>A1: =IF(A3="",0,IF(A2=0,A1,A1+1))</P><P>A2: =10-A1^3-A1</P><P>A3: empty, initially</P><P> </P><P>Alternatively:</P><P>A1: =IF(A3="",0,IF(A2=A1,A1,A1+1))</P><P>A2: =10-A1^3</P><P> </P><P>Enter anything into A3 to start the iterative calculation. Delete the value in A3 to start over.</P><P> </P><P>For grins, change 10 in A2 to 2, 30, 68, 130, etc.</P><P> </P><P>Remember to delete the value in A3 before changing A2. Otherwise, we get the wrong result because A1 does not start at zero.</P>Sun, 07 Nov 2021 03:17:24 GMThttps://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2935056#M121278Joe User2021-11-07T03:17:24ZRe: Iterative calculations return #NUM
https://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2940133#M121490
Thanks! This works for me seems a bit complicated for such calculations but at least it works. Cheers!Mon, 08 Nov 2021 17:18:15 GMThttps://techcommunity.microsoft.com/t5/excel/iterative-calculations-return-num/m-p/2940133#M121490davididofff2021-11-08T17:18:15Z