Circular References - Calculate

Question

Level 1

0 points

Circular References - Calculate

I imported a worksheet that I created in Excel that uses a circular reference and therefore requires the software to calculate on an iterative basis the value of a particular cell. In Numbers, the formula results in an error that says the formula "cannot depend on another formula that references this cell." This is a basic function in Excel which allows you to adjust the "calculation" settings including setting the maximum number of iterations it will cycle through in order to identify the value. Does Numbers have a similar ability? If so, how do I turn it on?

Thanks in advance for the help!

iMac 24" / MacBook Pro, Mac OS X (10.6.3)

Posted on May 9, 2010 5:52 PM

Reply

Answer 1

Best reply

Badunit

Level 7

25,856 points

May 10, 2010 2:54 AM in response to chriskelsey

Numbers does not support circular references and iteration. You can, however, create a short script that would let you iterate. Here is a quick example:

The document:
B2 = number of iterations
B3 = initial value
B4 = the formula (which will have the result of the first iteration)

The script:
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
set v to value of cell "b4"
set iterations to (value of cell "b2") - 1
repeat with i from 1 to iterations
set value of cell "b3" to v
set v to value of cell "b4"
end repeat
end tell

Reply