how can I do some iterative calculus in Numbers?

Excel allows circular references if you limit the number of iterations in Excel/Preferences/Calculation. Numbers does not have that preference or the ability to do iterations. However, you can do it easily with Applescript. A simple script that repeatedly takes the value from one cell and places it into another is all that is required. The Numbers table would be designed to have an input cell for the "seed" value, the formulasand a cell for the result based on that input. No circular references. You might also have a cell to specify the number of iterations. The script would do the iterating by repeatedly taking the result and placing it in the input cell.

Here is a simple example. First image is before running the script. Note that one iteration has already been done by the table itself:

Here is the script. Note the delay. It may not be required but is there to ensure the calculations i nthe table complete. I set it to 1/2 second so you can see it running (assuming you try this out).

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1

set value of cell "B5" to 1 -- this is the counter for iterations. One iteration is already complete

repeat while value of cell "B5" < value of cell "B4" -- B4 has the number of iterations

set value of cell "B2" to value of cell "B3"

set value of cell "b5" to (value of cell "B5") + 1

delay 0.5

end repeat

end tell

Here is the result:

Better depends on what you are doing. If circular references are a requirement then Excel may be better for that job. If you need to process 10000's of thousands of data points Excell is better. For many other tasks Numbers is more fun to use.

Maybe you can desribe what you are doing and the people in the formu can help determine if Numbers can do the job.

If you do not have Microsoft Excel you can try LibreOffice before purchasing Excel. It can handle very large files. LibreOffice does not allow circular references either. When I check MS Excel it doesn't allow circular references either. So you must be asking about something else. Can you describe your specific problem?

In fact, it means that the applications weren't designed to target the same kind of users.

The result is that we have two different applications.

Each of them is supposed to fit the needs of the targetted customers.

So no one is better than the other one.

Everybody may see that a Chinese citizen and a Germany one are different.

But we have no reason to say that one of them is better than the other one.

From my point of view it's the same for the two apps.

Yvan KOENIG (VALLAURIS, France) jeudi 23 février 2012

iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.3

My Box account is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

Hi,

Unless I am wrong, circular references are quite usual in financial models used in investment banking purposes e.g. M&A, it is even part of professional bankers' training to learn how to activate and use them, e.g. to model (basic) debt schedules.

Typically in debt modelling, you will use interests calculated as the average of the beginning and the end balance for the period because it leads more accurate results than if you take the rate multiplied by the last balance, esp. in case where the balance varies greatly.

So, back to the model, the interests flow into the net income, the net income flows into the cash flow, the cash flow determines the ending amount in the balance sheet, and the end balance is used to calculate interests. QED ;-). To enable the iteration you just click it in the Excel options (formulas) and set a max number of iterations (e.g. 1000)

So, now to my problem: I am used to handle circular references in MS Excel, which in many ways is a great product, but I am relatively new to Mac. I have the currently last version of Numbers ('09) and I am reviewing in Numbers a model built in Excel with circular references. And of course, the whole circular references are blocked or suppressed by Numbers with a red triange in the cell and some comments.

So my question is, has there been an update of Numbers on this (I have read the discussion before and also from other forums I understand that, at least until the beg of the year, Numbers does not support circular references), but is it possible in between to activate a circularity in Numbers or what can you do to work on these types of financial models with Numbers? Or is it not possible in my sense described above and I should by Excel for Mac (rather expensive)?

Thanks for answers/comments,

R

Ren,

You should look at Excel if Numbers doesn't meet your needs. It's difficult to understand how your professional activites could require Excel and not justify the expense. If money is the true issue, look at a free Office clone such as LibreOffice and see if it meets your needs.

Jerry

Yes Jerry,

Thx for the answer - I come to the same conclusion. Excel remains Excel and is probably in some ways unreplacable, and as for the price, as always, it is a question of budget allocation :-) - My next iPhone will have to wait a bit maybe... :-)

Cheers, R

Hi Badunit,

Thx for the answer - it's fine. But too complicated to me and not practical to set up in a financial model. I'd rather have a soft that just allows it, I am not a developper... But LibreOffice seems quite good, and allows iterations...

Best, R

Dear Gents,

Life is sometimes funny :-)

I am just being told by this client 5mn ago that he is so happy with the (Excel!! ;-) ) model I sent yesterday evening to negotiate with his bank, he has actually already ordered and is sending me the iPhone5 at stakes if we met the deadline...

So I don't know who to thank, God, Bill or Steve/Tim, probably all of them... ;-)

Cheers, R

It depends on what you mean. Can state a more focused and direct question?

You cannot have circular references in Numbers

R,

Good luck. Hopefully it won't take too long to pay off that new software.

Jerry