5 ms·

Can someone explain why Excel disallows cycles (recursion) in formulas?

CurtHagenlocher 14y ago
This is configurable. You can enable recursion and set a max for the number of iterations. You can implement relaxation-based differential equation solvers that way :). Edit: You can also solve Sudoku this way: http://office.microsoft.com/en-us/excel-help/microsoft-excel-blog-building-a-sudoku-solver-using-excel-iterative-calculation-HA010293321.aspx http://office.microsoft.com/en-us/excel-help/microsoft-excel...
KMag 14y ago
The more general answer is that cell updates could just keep going forever, never settling down, or at least taking a long time to settle down. There isn't a good one-size-fits-all way to resolve this. Plus, these sorts of structures are most likely typos by the people writing the spreadsheets. The more specific answer is that Excel performs a topological sort on the cell dependency graph in order to update cells in an efficient order. Edit: as someone else has pointed out, if you really want cycles, there's an option to tell Excel how to terminate computation for your particular infinite loop use case.
manifold 14y ago
It's possible to turn on circular calculations and to limit the number of iterations at a global level (I've never seen it limited at a local level), which could be used as recursion to some extent. I think it would be quite rare to find a case where circular calculations would be a better solution than doing the same thing in a macro, writing a custom function or finding a closed form solution. The biggest problem with circular calculations is that they may not converge fast enough (before the limit is reached), and if the output is important that's not a good feature to have.
niggler 14y ago
There is a way to enable iterative calculations (which allows you to use cycles) (in the formula options) but note that it is disabled by default.
mdda 14y ago
Suppose the formula in A1 is =A1+1. What should be the displayed value in A1? How long should it take to display that number? This is a simple case, which clearly diverges. There is no simple way to determine from a general formula whether it will be convergent or divergent. In the interests of definiteness, Excel defaults to just disallowing formulae with this kind of indecisive output.

This is configurable. You can enable recursion and set a max for the number of iterations. You can implement relaxation-based differential equation solvers that way :). Edit: You can also solve Sudoku this way: http://office.microsoft.com/en-us/excel-help/microsoft-excel-blog-building-a-sudoku-solver-using-excel-iterative-calculation-HA010293321.aspx http://office.microsoft.com/en-us/excel-help/microsoft-excel...

KMag 14y ago

The more general answer is that cell updates could just keep going forever, never settling down, or at least taking a long time to settle down. There isn't a good one-size-fits-all way to resolve this. Plus, these sorts of structures are most likely typos by the people writing the spreadsheets. The more specific answer is that Excel performs a topological sort on the cell dependency graph in order to update cells in an efficient order. Edit: as someone else has pointed out, if you really want cycles, there's an option to tell Excel how to terminate computation for your particular infinite loop use case.

manifold 14y ago

It's possible to turn on circular calculations and to limit the number of iterations at a global level (I've never seen it limited at a local level), which could be used as recursion to some extent. I think it would be quite rare to find a case where circular calculations would be a better solution than doing the same thing in a macro, writing a custom function or finding a closed form solution. The biggest problem with circular calculations is that they may not converge fast enough (before the limit is reached), and if the output is important that's not a good feature to have.

niggler 14y ago

There is a way to enable iterative calculations (which allows you to use cycles) (in the formula options) but note that it is disabled by default.

mdda 14y ago

Suppose the formula in A1 is =A1+1. What should be the displayed value in A1? How long should it take to display that number? This is a simple case, which clearly diverges. There is no simple way to determine from a general formula whether it will be convergent or divergent. In the interests of definiteness, Excel defaults to just disallowing formulae with this kind of indecisive output.