Forum Discussion

Viz's avatar
Viz
Brass Contributor
Jan 05, 2021

Lambda Example: Generate Fibonacci series

In this post, I would like to explain how I have used Lambda to create a function to generate a Fibonacci series array.   This example can also be used to understand how to create an array where th...
excel
office 365
davidleal's avatar
davidleal
Iron Contributor
Apr 13, 2023

PeterBartholomew1 Thanks, that is the issue, then it is a limitation for any solution starting from 74 Fibonacci numbers because it has 16 digits. The reason the Binet formula doesn't get the correct result is not its own calculation, but rather the reason you mentioned: https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resultso if that limitation affects all possible formulations, then per my understanding from this post, the Binet formula is the best approach in Excel to get a Fibonacci number up to 73 and therefore any series of Fibonacci numbers in Excel is accurate only up to that number. For numbers greater than 73 Excel is not a good approach. 

mtarler's avatar
mtarler
Silver Contributor
Apr 14, 2023

davidleal ok so this is probably NOT the most efficient but I created a BigAdd LAMBDA function to handle these big numbers correctly:

 

BigAdd = LAMBDA(A, B, [c], let(cc, if(isomitted(c),0,c), 
            if((len(a)>14)+(len(b)>14), 
            let(lowadd, right(a,14)+right(b,14)+cc,
              ccc, if(len(lowadd)>14,1,0),
              aaa, if(len(a)>14,left(a,len(a)-14),0),
              bbb, if(len(b)>14,left(b,len(b)-14),0),
              bigadd(aaa,bbb,ccc)
              & right(lowadd,14)&""),
            (a+b+cc)&"")))

 

I then used that BigAdd in a simple REDUCE - LAMBDA function and it seems to work:

 

=CHOOSECOLS(REDUCE({1,1},SEQUENCE(A1),LAMBDA(p,q, 
    LET(prev, CHOOSEROWS(p,-1), 
       VSTACK(p, HSTACK( CHOOSECOLS(prev,-1), BigAdd(INDEX(prev,1),INDEX(prev,2)))))))
  ,1)

 

I'll leave it to you to see if using this BigAdd or similar concept can be used in the other approach(s)

Resources