A non-VBA recursive factorial function?

Question

Is it possible to, by enabling circular references (select enable iterative calculation in the Formulas part of Excel Options), create a recursive factorial function in Excel? I know about FACT() of course and am not looking for a practical way to compute factorials. Rather, my goal is to find a way to exploit circular references as a general tool for creating and using recursive functions in Excel and factorials provide an interesting test case.

Using an idea from Jan Karel Pietrerse's website I am able to get close but the resulting function depends on two cells rather than just one, so it doesn't solve the problem. In

I created named ranges using the strings in the top row. In the cell now named factorial I entered:

=IF(initializing,1,IF(factor=0,factorial,factorial*factor))

and in cell factor I entered:

=IF(initializing,n,IF(factor=0,factor,factor-1))

The above image shows what things look like when n = 10 and initializing = True. The formula in factorial corresponds to a standard trick in functional programming to make a recursive function tail-call recursive by introducing a helper function with an accumulating parameter. The problem is that helper function needs to be called and the formula in factorial is in some sense both the function itself and its helper function, with the contents of initializing determining what role it is currently playing.

What I have works in the sense that if I switch the value of initializing away from True (e.g. just delete it) then the value of factorial becomes the correct factorial:

Can initializing be removed from the picture? Is it possible to modify the set-up so that if e.g. n is changed to 5 then factorial instantly changes to 120 without needing to first set and then change some other cell? I've tried a couple of different things but keep ending up with 2-step rather than 1-step functions. Maybe some wizardry involving array formulas?

Answer 1

Here is a formula with only one helper cell, updating automatically whenever n is changed, of course the helper column is more complicated compared to your original one:

• helper: =IF(C2<>TEXT(A2,"0"),IF(ISNUMBER(C2),IF(C2=A2,TEXT(A2,"0"),C2+1),1),C2)
• factorial: =IF(ISNUMBER(C2),IF(C2=1,1,C2*D2),D2)

The key is to change the helper cell when the result is reached (could be also negated, completed with a text (e.g. A1&" Finished", the important to make it clear it reached end of calculation and also keep it comparable with the input cell).

Just for fun, array formula without iteration in F2: =PRODUCT(ROW(INDIRECT("a1:a"&A2,TRUE)))

Update

Formulas step by step:
helper:

• stable state is formatted as text, as far as value of text is the same as n nothing happens, formula doesn't change the value in helper
• once n is changed: first criteria C2<>TEXT(A2,"0") will be false, but helper is still text, so second criteria is also false, helper is reset to 1
• after that helper is incremented until it reach n, when target is reached helper is converted to text to mark that calculation is finished

factorial:

• while helper is text nothing happens
• once helper is number: if it's 1 then factorial is reset to 1, else multiplication helper * factorial is calculated

Answer 2

Yes it is possible to remove "initializing" variable from the algorithm.

Let's look for an example.

I got 3 cells called: number, factorial, and iteration.

First of all, main thing that you should remember while using iteration calculations is the IF statements checking for 0, because the empty cell is 0 by default, and, as we know, zeros in the multiplications (the main part of factorial) is bad idea.

The iteration cell got this formula: =IF(i=0, number+1,i-1)

The factorial cell got this formula: =IF(factorial=0, 1, IF(i=1, factorial, factorial*i))

So if in the iteration cell is 0 or 1 (it depends on excel choice, and moon phase also) in the factorial cell you should get the right answer.

Of course by changing the number you can notice that the iteration cell is not 0 or 1, and the factorial not looks like expected result. Try to press F9 until you don't get it.