Excel Formula Loop Error

[108monkeys]

I am trying to use formulae for a calculator which uses a minimum working amount of $30 (see A2:A12) and increments this to 10% once it is over $500. It then calculates returns (F2:F12) based on yes (C2:C12) and/or no (C2:C12).

This works fine when I am using a test balance (G2:G12) to calculate the amount (A2:12) and the dividends (F2:F12) but when I try to change the test balance (G2:G12) as is shown the actual balance values (H2:H12) by adding the result as shown in the formula of the actual balance (I2:I12), the starting value (A2:A12) seems to loop and returns a zero value in the test balance (G2:G12).

How can I use the actual balance even though the amount and return are based on this value?

Is there a better way of doing this which is Excel friendly?

Hope you can help.

Thanks, Hanuman.

Note: This post has been added again as I was unable to update the above image as it was not showing correct cell references and may possibly be why there was no responce.
<!-- / message -->

 

[108monkeys]

I found the solution. Error caused by the number of iterations. This allowed for the looping. See the below Excel Help for instructions:

1. <LI class=LPROC>On the Tools menu, click Options, and then click the Calculation tab. <LI class=LPROC>Select the Iteration check box. <LI class=LPROC>To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum iterations box. The higher the number of iterations, the more time Excel needs to calculate a worksheet.
2. To set the maximum amount of change you will accept between calculation results, type the amount in the Maximum change box. The smaller the number, the more accurate the result and the more time Excel needs to calculate a worksheet.

Thanks for reading.