 # Itteration error

I have made a calculation sheet for work, with an iteration in it, that works. Only to crash when a parameters switches between "Yes" or "No". Now when K82 changes between "Ja" and "Nee"  (Yes or No) the iteration doesn't work anymore as shown below So the issue occures in K75 & K81. It gives an #WAARDE error (#VALUE error). And I don't know why it is doing this.
I have used an IF function in both those cells, so that when K82=Yes -> "does not apply"

Any suggestions on how I can get this working again? so when K82 changes, the itteration functions keep working.

Iteration Functions:

v_1 (K75) = IF(K82="Nee";1-(SIN(4*PI()*fTpi-2*Gamma)+SIN(2*Gamma))/(4*PI()*fTpi)+(f*Tau)/fTpi*(1-EXP(-(2*fTpi)/(f*Tau)))*SIN(Gamma)^2-(8*PI()*f*Tau*SIN(Gamma))/(1+(2*PI()*f*Tau)^2)*((2*PI()*f*Tau*(COS(2*PI()*fTpi-Gamma)/(2*PI()*fTpi))+SIN(2*PI()*fTpi-Gamma)/(2*PI()*fTpi))*EXP(-(fTpi/(f*Tau)))+(SIN(Gamma)-2*PI()*f*Tau*COS(Gamma))/(2*PI()*fTpi));"N.v.t")

fTpi (K81) = IF(K82="Nee";v_1/WORTEL(v_2);"N.v.t")

I hope it's somewhat clear, and thanks in regards.

Bart

3 Replies

# Re: Itteration error

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Itteration error

In this link should be the excel document with the issue. I have marked yellow which parameter you can alter.

# Re: Itteration error

I cannot get the calculation to work at all. If I change the iterative calculation parameters, I sometimes get #DIV/0! (in Dutch #DEEL/0!), but mostly #VALUE!, never a number.

And I'm afraid the formula for v2 is far too complicated (for me) to analyze.

As an alternative, here is a macro that performs an iterative calculation:

``````Sub Recalc_fTpi()
Const Precision = 0.0001
Dim Test As Double
Application.ScreenUpdating = False
Application.EnableEvents = False
If Range("M12").Value = "Nee" Then
Range("fTpi").Value = 10
Do
Test = Range("v_1").Value / Sqr(Range("v_2").Value)
Range("fTpi").Value = (Test + Range("fTpi").Value) / 2
Loop Until Abs(Test - Range("fTpi").Value) < Precision
Else
Range("fTpi").Value = "N.v.t."
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub``````