Is there a way to calculate with iteration like in Excel? I have an excel spreadsheet that I would like to convert to FIlemaker Pro, but it contains a circular reference: Total= subtotal + (x% * Total). In excel this is a circular reference, but can be done by changing the calculation preferences to iterate a max of 100 times with 0.001 max change.

Thanks

Not sure what exactly you are asking (perhaps come up with a better example, or even better: the real problem).

Within a Let() function, you can redefine variables, e.g.

Let ( [

a = 5 ;

a = a + 1

] ;

a

)

This returns 6, but iteration is not required here. For a truly iterative/recursive calculation, you need to use either a repeating calculation field or a custom function.

Let me be more specific in what I am trying to accomplish. Here is an example that is performed in excel:

MANUFACTURING COST (A) $50.00

G&A & SELL( 11% $6.96

PROFIT © 10% $6.33

SELLING PRICE (D) $63.29

Here is the relationship of the above variables:

A is an inputted amount.

B=(.11 * D)

C=(.10 * D)

D=A + B + C

There is a circular reference between B, C, and D. In excel, this is calculated by iteration in the preferences. In simple terms, when a value for A is input, D will calculate multiple times until it's value stops changing. When manually done this takes about 10 calculations until it remains constant (like a sine wave who's amplitude gradually reduces to 0).

After reviewing your last post, it seems like I will have to define some type of repeating calculation function to continue to recalculate? If this is correct, how do I do this?

Thanks

I have seen this called "fixpoint evaluation", you keep evaluating until the answer stops changing.

However, for this simple example, A is clearly 79% of D, so D can be directly computed as:

  D = A / .79

and there's no need for the recursion.

I still don't see why this requires a recursive calculation - in Excel, or anywhere else. What am I missing here:

A is an inputted amount.

B = 0.11 * D

C = 0.10 * D

D = A + B + C

D = A + 0.11 * D + 0.10 * D

D - 0.11 * D - 0.10 * D = A

(1 - 0.11 - 0.10 ) * D = A

D = A / (1 - 0.11 - 0.10 )

D = A / 0.79

50 / 0.79 = 63.291139240506