excelrecursionexcel-formula

A non-VBA recursive factorial function?

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

enter image description here

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:

enter image description here

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?

Solution

  • 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:
    enter image description here

    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:

    factorial: