Home
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 show-stopper for me. Does anyone know how to get around this apparent inability of Numbers to solve circular references?
I'm no spreadsheet expert, and i haven't done anything that heavy in Numbers yet (though i did design a variable-interest 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).]
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.
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.
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.
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.
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 "="
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...
Just as a proof of concept, I set up a Numbers spreadsheet to find zeroes of a 5th-degree 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 Newton-Raphson. 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:
Code:
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.
Originally Posted By: Virtual1
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.
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.
Originally Posted By: JoBoy
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.
This was discussed on the Apple Discussion Forum earlier this year; it may help you.

Apple Forum: Numbers and Circular References
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.
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.
Somewhat off-topic 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.
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.
Code:
-- 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.0E-9
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
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'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 Nelder-Mead optimization, which should be adaptable to the circular reference calculation problem discussed here, although you would need to restructure your calculation. I posted the Nelder-Mead solver code on the OpenOffice Forum board here:
http://www.oooforum.org/forum/viewtopic.phtml?t=92089
It's just a matter of copying the code and pasting it into the macro editor. The Nelder-Mead 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.
Originally Posted By: Bob_00001
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.
© FineTunedMac