#13585  01/06/11 04:27 PM
Apple's Numbers can't solve circular references?

Registered: 08/05/09

I tried to get Apple's Numbers app to solve circular references, but all I got was a warning: "This formula can't reference its own cell or depend on another formula that references this cell." Normally, this is good advice, but there are some problems that can't be solved without a circular reference. MS Excel is very good at this and does it through iteration until a solution is reached. Excel's Preferences allow the user to set the Maximum Iterations and the Maximum Change between iterations in order to arrive at a solution. Sometimes, a solution cannot be reached within the specified number of iterations, but most of the time, the problem is solved. In my work, this feature is exceedingly valuable.
I'm an Apple fan and would love to be able to use Numbers instead of Excel, but this is one showstopper for me. Does anyone know how to get around this apparent inability of Numbers to solve circular references?
Edited by JoBoy (01/06/11 04:28 PM)
_________________________
Mac Pro dual QuadCore Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top



#13586  01/06/11 05:41 PM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 09/03/09
Loc: 10.6.8 (build 10K549)

I'm no spreadsheet expert, and i haven't done anything that heavy in Numbers yet (though i did design a variableinterest mortgage analyzer from scratch once in ClarisWorks, sans template). But isn't it customary to add a hidden column and use the cells there as a sort of scratchpad for intermediate values and/or ancillary control structures? [as indicated —once set up —such cells remain hidden (unseen and unprinted).]

Top



#13591  01/06/11 09:57 PM
Re: Apple's Numbers can't solve circular references?
[Re: Hal Itosis]

Registered: 08/05/09

That's an interesting concept. I've never had to use it. I'm not a programmer, so I don't really know how they set up iterations. I just know it works.
Excel looks at the rest of a formula and then apparently starts out using trial numbers and then moves toward a result that's in the ballpark of what the equation is trying to do. If it finds a "solution," it stops and I look at it to see if it fits. Usually, it does. Sometimes Excel is stumped and I have to figure out a different approach. I depend on that feature for important stuff, so I'd rather stay with Excel than try to manufacture something. I'm just not qualified to do that. I wish Apple would make a serious effort to make Numbers a functional equivalent to Excel. MS keeps messing around with Excel to the point I don't trust them when they announce a new version. Several years ago, I spent a lot of time putting together macros that made my work much easier. It was a major investment in time. Then they eliminated the feature and told us to use Visual Basic. To me, that meant sorry, chump, your work is dead and you'll have to use Visual Basic instead. Great. I don't know how to use it and I don't have the time to learn it. I found other ways to rig the spreadsheets to do what I wanted, but I'd go back to the old way in a heartbeat if they restored the macros they once had. (end of rant) Thanks for your interest. Also, thanks again for the info regarding disk permissions. That was a huge help. Best regards.
_________________________
Mac Pro dual QuadCore Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top



#13604  01/07/11 09:22 AM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 08/13/09
Loc: California

Hal is correct. The "standard" approach in Numbers is to use a hidden column or even have an entirely separate sheet and/or table that holds the necessary data.
I can imagine times when circular references are the desirable way to go, but they still are "bad programming". You should not rely on the software developer to resolve the problem.
_________________________
On a Mac since 1984. Currently: 27" iMacs, Macbook Air, macOS 10.15.x,; iPhones, iPods and iPads galore!

Top



#13623  01/08/11 09:18 AM
Re: Apple's Numbers can't solve circular references?
[Re: Ira L]

Registered: 08/05/09

If I had the "necessary data" I wouldn't need circular references. When I get a suggested answer, I cross check it extensively to see if it fits the rest of the related calculations. If so, I use it. If not, I try again or change my approach. For me, Excel has provided a very reliable solution to problems where I have one too many unknowns, but I never take a suggested solution at face value without extensive testing.
_________________________
Mac Pro dual QuadCore Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top



#13636  01/09/11 09:51 AM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 08/13/09
Loc: California

I see where you are coming from and for that the Excel approach makes sense.
I don't know what you are trying to do, but perhaps a more specialized application would be better.
_________________________
On a Mac since 1984. Currently: 27" iMacs, Macbook Air, macOS 10.15.x,; iPhones, iPods and iPads galore!

Top



#13637  01/09/11 10:45 AM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 08/04/09
Loc: Iowa

but there are some problems that can't be solved without a circular reference.
Can you provide an example? I'd tend to think that in all cases a little algebra could be used to move all the cell's terms to be alone on the left of the "="
_________________________
I work for the Department of Redundancy Department

Top



#13639  01/09/11 11:52 AM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 08/04/09

That's an interesting use of circular references. Normally, spreadsheets insist that the dependencies between cells form a DAG (Directed Acyclic Graph), where "acyclic" is another way to say "no circular references".
But I can see that it would be useful for iterative techniques. You have an "input" cell that contains a guess at some value you want to find. The spreadsheet grinds away to produce, in an "output" cell, a hopefully better guess. So far, no cycles.
You introduce a cycle by, in effect, copying the output back to the input. You could do that by making the output and input the same cell, but for the sake of discussion let's suppose they're different cells.
I've seen other spreadsheets (all obsolete, unfortunately) that would iterate for you, but they did it as a special operation, not in response to circular references. Hewlett Packard pocket calculators had it built in, as a SOLVE function; you programmed a function and gave an initial guess or two, and it would try to home in on a zero of the function.
In Numbers, perhaps you could make the iteration manual. Write an AppleScript to copy the value from the output cell back to the input cell. Choose standard names for the cells so that you'd only need to write one AppleScript for any number of spreadsheets. Then repeatedly run the AppleScript (probably from the Script menu) to watch it converge.
Just a thought...

Top



#13642  01/09/11 01:41 PM
Re: Apple's Numbers can't solve circular references?
[Re: ganbustein]

Registered: 08/04/09

Just as a proof of concept, I set up a Numbers spreadsheet to find zeroes of a 5thdegree polynomial. One table, named "Guesses", contained in its first row a current guess, and computed in its second row a next guess. Column 1 of the "Guesses" table contains labels, so the current and next guess are actually in column 2. How the next guess is computed isn't particularly germane here, but since the derivative of a polynomial is easy to evaluate I used NewtonRaphson. Since this was just proof of concept, I didn't bother defending against degenerate cases, like when the slope is zero.) For problems where the derivative is intractable, the secant method could be used instead, or even bisection. Another table contained the coefficients, plus some workspace to evaluate the the polynomial and its derivative at the current guess. Then I wrote the following AppleScript: tell application "Numbers"
tell document 1
tell sheet 1
tell table "Guesses"
set value of cell 2 of row 1 to value of cell 2 of row 2
end tell
end tell
end tell
end tell Put in an initial guess, and keep clicking on the "Run" button in AppleScript Editor" to watch the spreadsheet converge on the solution. Works pretty well in the test case.

Top



#13643  01/09/11 01:47 PM
Re: Apple's Numbers can't solve circular references?
[Re: Virtual1]

Registered: 08/05/09

but there are some problems that can't be solved without a circular reference.
Can you provide an example? I'd tend to think that in all cases a little algebra could be used to move all the cell's terms to be alone on the left of the "=" Actually, I can't provide it. It's highly proprietary pertaining to very complex chemical reactions, but there are practical reasons why I also can't set up Numbers in the ways that have been suggested. The main one is the sheer magnitude of trying to do it. Another one is I'm not really qualified to do it. Here's an example of what I deal with routinely: First, in one example, I have a formula that involves one circular reference and reference to nine other cells many of which refer further to other cells. That single formula involves 5 multiplications, two additions, two subtractions, and one division. Second, Excel usually goes through several hundred or sometimes several thousand iterations before finding the "answer." In Excel 2008/Mac, you go to "Preferences: Calculation" and set the "maximum iterations". I use 10,000. I also set "Maximum change" to 0.001 (meaning that, if the iteration change gets smaller than that, the calculation stops. To me, that should be labeled "minimum allowable change." Then select "Calculate Sheets: Automatically" and Excel goes to work. The labeling in this calculation function doesn't distinguish between circular references and unknown values where circular references are not involved. It handles them all. Unless Numbers is expanded to possess the functionality and capacity of Excel, I'm afraid I'm stuck with Excel. Before I started this thread, I had spent quite a bit of time snooping around Numbers and its User Guide, but I couldn't find anything on the kind of calculations mentioned here, so I thought I'd better call in the heavy artillery in this forum just in case I'd missed something. From this discussion, it appears that Numbers doesn't yet have all of Excel's calculation features. Since I don't consider myself qualified to produce a do it yourself solution, it seems my only real option is to stick with Excel. I appreciate the suggestions very much, but I think I need to stay where I am until Numbers is brought up to Excel's capacity. BTW, I really enjoy this forum. You folks are smart, knowledgable, and very experienced. Thanks for the input.
_________________________
Mac Pro dual QuadCore Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top



#13644  01/09/11 02:05 PM
Re: Apple's Numbers can't solve circular references?
[Re: ganbustein]

Registered: 08/05/09

Thanks for the assist. As mentioned in another post, I'm just not qualified to try to devise an alternative way to process circular references. The circular reference I referred to in the other post does not contain a guess. It is set up so that the ultimate value of the circular reference equals the value of the cell in which it is located. Sorry, but I just can't say anymore. Best regards.
_________________________
Mac Pro dual QuadCore Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top



#13649  01/09/11 07:53 PM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 08/03/09
Loc: Portland, Oregon, USA

I tried to get Apple's Numbers app to solve circular references, but all I got was a warning: "This formula can't reference its own cell or depend on another formula that references this cell." Normally, this is good advice, but there are some problems that can't be solved without a circular reference. MS Excel is very good at this and does it through iteration until a solution is reached. Excel's Preferences allow the user to set the Maximum Iterations and the Maximum Change between iterations in order to arrive at a solution. Sometimes, a solution cannot be reached within the specified number of iterations, but most of the time, the problem is solved. In my work, this feature is exceedingly valuable. Wow, that's interesting. It would never occur to me to actually use a circular reference intentionally in a spreadsheet. I do not believe there is any way to do what you're describing in Numbers. I'm frankly a bit surprised that Excel can do it. That must have been something of a programming nightmare, I reckon.

Top



#13650  01/09/11 08:06 PM
Re: Apple's Numbers can't solve circular references?
[Re: tacit]

Registered: 08/04/09

This was discussed on the Apple Discussion Forum earlier this year; it may help you. Apple Forum: Numbers and Circular References
_________________________
MacBook Pro 15” Matte, OS X 10.6.6 Scrivener 2, Mellel 2.7.3, DevonThinkPro 2, Accordance 9.1, OpenOffice.Org 3.3, Notebook 3, Reunion 9, Tinderbox 5.6.1

Top



#13651  01/09/11 08:14 PM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 08/04/09

Whether you think of it as a guess, that's exactly how Excel does it. It plugs a number (possibly zero) into some cell in the cycle, and then computes everything (including that cell) based on that assumption. The newly computed value is the next guess. It repeats until the value converges, if it does.
Break the cycle. To do that, copy the formula from your "answer" cell into some new cell (which I'll call the "next guess" cell) and remove the formula from the "answer" cell. Nothing else in the spreadsheet needs to change. Convergence means the value in the "next guess" cell, as calculated by all the formulas in your spreadsheet, matches the value in the "answer" cell.
I showed you an AppleScript that would iterate once, but it could be easily expanded to iterate 10,000 times if that's what you want. On each iteration, it compares the values in the "answer" and "next guess" cells. If they're close enough, it quits. Otherwise it copies the "next guess" value into the "answer" cell, which triggers Numbers to iterate one more time. Count the iterations (or look at the elapsed wall clock time) and abort if convergence is taking too long.

Top



#13653  01/09/11 08:45 PM
Re: Apple's Numbers can't solve circular references?
[Re: ganbustein]

Registered: 08/05/09

ganbustein & GrayShades: I really appreciate your obviously knowledgeable assistance, but I don't know much about scripting at all. I wouldn't know how to set up either of your suggestions and it's not right to ask you to do it. I just don't know what else to say. I can't write either suggestion for Numbers, but I can flog Excel because there's really nothing to set up except a couple of parameters.
_________________________
Mac Pro dual QuadCore Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top



#13659  01/10/11 08:01 AM
Re: Apple's Numbers can't solve circular references?
[Re: ganbustein]

Registered: 08/13/09
Loc: California

Somewhat offtopic comment: it is interesting to see the "peripheral" knowledge that exists in this forum behind the cute on line names and avatars. As I was reading through some of the posts I was wondering if Newton's method would be brought up, and sure enough (along with apologies for not including the degenerate case!), there it was. Way to go people!
On topic comment: Numbers is not Excel and I imagine has no intention of becoming equivalent. It is the spreadsheet for the rest of us.
_________________________
On a Mac since 1984. Currently: 27" iMacs, Macbook Air, macOS 10.15.x,; iPhones, iPods and iPads galore!

Top



#13660  01/10/11 11:07 AM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 08/04/09

Just for the benefit of anyone else finding this thread, here's the AppleScript that incorporates my suggestions. To recap: Break the cycle by splitting one cell into two: create a new cell (the "output" cell) into which you copy the formula from the original cell. Remove the formula from the original cell (the "input" cell), so it becomes an ordinary data entry cell. Nothing else needs to change. In particular, all formulas that referred to the original cell still do, and no formulas refer to the new cell. Convergence means the values in the "input" and "output" cells are (approximately) equal. Customize the following AppleScript and run it. The customizations all appear near the top of the script, and correspond roughly to entering values into Excel's preferences to limit the number of iterations and set a convergence factor. I've added the ability to set a time limit rather than (or in addition to) an iteration limit, something that Excel doesn't provide but that I think is actually more meaningful. In the example, the limits are encoded into the AppleScript, but they could equally well be pulled from the spreadsheet itself.  maximum number of iterations. 0 means unlimited
set iteration_limit to 100
 relative precision required. 0.0 means must be exact
set precision to 1.0E9
set deadline to (current date) + 5 * minutes
tell application "Numbers"
set inputCell to a reference to cell 2 of row 1 of table "Guesses" of sheet 1 of document 1
set outputCell to a reference to cell 2 of row 2 of table "Guesses" of sheet 1 of document 1
 ***** Only the part of the script above this line needs to be customized ***** 
set oldvalue to value of outputCell
repeat
 always do at least one iteration
set value of inputCell to oldvalue
set newvalue to value of outputCell
 close enough?
if my closeenough(oldvalue, newvalue, precision) then exit repeat
set oldvalue to newvalue
 too many iterations?
 test for =0 instead of <0, so setting limit to 0 will mean unlimited
set iteration_limit to iteration_limit  1
if iteration_limit = 0 then exit repeat
 out of time?
if (current date) comes after deadline then exit repeat
end repeat
end tell
on abs(x)
if x < 0 then return x
return x
end abs
on max(a, b)
if a > b then return a
return b
end max
on closeenough(a, b, prec)
if a = b then return true  exact is always close enough, even if prec = 0.0
return abs(a  b) < prec * (max(abs(a), abs(b)))
end closeenough

Top



#13662  01/10/11 01:01 PM
Re: Apple's Numbers can't solve circular references?
[Re: JoBoy]

Registered: 08/04/09
Loc: Iowa

I would suggest hiring someone to program something to perform these solves for you. Should be very easy to code in RB/VB. and a lot more effective than a spreadsheet.
_________________________
I work for the Department of Redundancy Department

Top



#13691  01/11/11 08:55 PM
Re: Apple's Numbers can't solve circular reference
[Re: JoBoy]

Registered: 01/03/10

I've pretty much abandoned Numbers because I find it rather underpowered. I'm now using NeoOffice Calc which is free, and I have written a macro to perform a NelderMead optimization, which should be adaptable to the circular reference calculation problem discussed here, although you would need to restructure your calculation. I posted the NelderMead solver code on the OpenOffice Forum board here: http://www.oooforum.org/forum/viewtopic.phtml?t=92089It's just a matter of copying the code and pasting it into the macro editor. The NelderMead solver can handle essentially an unlimited number of variables, and is more versatile and easier to implement than Newton Raphson because it's not necessary to calculate derivatives. I've used it extensively to solve equations which cannot be expressed in closed form. If you want to try this and have any questions about it, I'd be happy to help. BTW, for anyone that tried early versions of NeoOffice or OpenOffice and were disgusted by the user unfriendliness, be advised that it's now vastly improved.
Edited by Bob_00001 (01/11/11 09:34 PM)
_________________________
MacBook Pro 15" (2015) Sierra 10.12.6

Top



#13694  01/12/11 07:00 AM
Re: Apple's Numbers can't solve circular reference
[Re: Bob_00001]

Registered: 08/04/09
Loc: Iowa

I've pretty much abandoned Numbers because I find it rather underpowered Agreed. Tho I still use Excel. I remember trying neooffice awhile ago and it wasn't very good, I'll have to give it a revisit.
_________________________
I work for the Department of Redundancy Department

Top




