3

So, I have to run a recursive function times but it's going to take too long to actually print out all the values. Thus, my professor recommended using Excel but I don't know Excel at all. I need help converting the code into Excel.

It's probably easy for someone who knows Excel.

def a(n):
    k=3.8
    if n==0:
        return .5
    else:
        return k*a(n-1)*(1-a(n-1))

for i in range(100):
    print(a(i))

All i know is that you use the lambda() function in excel

Snooch
  • 53
  • 3
  • Are you using pandas? lambda functions are usually functions great for creating a function that only contains simple expressions. – Mr_GamePLAY Apr 12 '23 at 17:41
  • No I'm not using pandas. I just looked up how to make a recursive function in Excel and the format was something like "=lambda(data, char, if)". I was hoping to replicate this python function but inside of excel. But if you know how to do this using pandas, i am willing to try that. – Snooch Apr 12 '23 at 17:48
  • 1
    Does your professor want you to use python to write to excel, replicate the program in VBA, or replicate the program using a cell formula which is what `lambda()` is? – Warcupine Apr 12 '23 at 17:51
  • all the professor wants is the results of the function from 0-100 and then i plot it on a graph in desmos but the function started to take minutes to calculate around the 28th loop, so she recommended using excel. I just don't know how to actually format the lambda() – Snooch Apr 12 '23 at 17:56
  • @Snooch sorry, it isn't really caching, but look at the code as it exists now – juanpa.arrivillaga Apr 12 '23 at 18:12

2 Answers2

3

This is the lambda formulation in Excel:

=LAMBDA(n,IF(n=0,0.5,LET(prev,a(n-1),3.8*prev*(1-prev))))

enter image description here

which can be tested as follows:

enter image description here

The available workspace for recursion is actually very limited in Excel and you will soon run out of resources unless you adjust the formula in a similar way to the Python solution.

In Excel you also have the option of referring to the previous cell so if you wanted to stay with the original formulation, with 0.5 in C1, you could enter this in C2 and drag down:

=3.8*C1*(1-C1)
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
2

You don't need to use excel. You just need to use a better algorithm. The easiest way to prevent the exponential time complexity is don't re-calculate the same value twice:

def a(n):
    k = 3.8
    if n==0:
        return .5
    else:
        x = a(n - 1)
        return k*x*(1-x)

for i in range(100):
    print(a(i))

In Python, you should avoid recursion, though, since Python doesn't optimize recursion and you will run out of stack space. This is easy to convert to an iterative algorithm, though:

def b(n):
    k = 3.8
    prev = curr  = 0.5
    for i in range(1, n + 1):
        curr = k * prev * (1 - prev)
        prev = curr
    return curr
juanpa.arrivillaga
  • 88,713
  • 10
  • 131
  • 172
  • The function was given by the professor and I'm assuming because she is a calc 2 teacher she doesn't know that much about it. It flew right over my head. Thanks. – Snooch Apr 12 '23 at 18:14
  • @Snooch also, look at my other solution, which avoids recursion (which is of limited utility in Python, because Python doesn't do tail-call optimization, basically, you will run out of stack space, but an iterative algorithm will work without requiring auxilliary space) – juanpa.arrivillaga Apr 12 '23 at 18:17
  • @Snooch also note, that this situation is a *classic* example you would cover in an algorithms course, usually though, the sequence would be the Fibonacci sequenve as an example. Search for "iterative versus naive Fibonacci sequence time complexity" to learn more if you are interested. – juanpa.arrivillaga Apr 12 '23 at 18:22
  • Yeah, I haven't learned too much about optimization but this makes sense. The topic was something like recursive sequences so I hadn't really thought of changing it to an iterative function. Yeah I remember talking about fib sequence. – Snooch Apr 12 '23 at 18:26