Forum Discussion
Lambda Example: Generate Fibonacci series
Please accept my apologies. I hadn't noticed your use of the Binet formula. I simply revisited the problem as an exercise, since the original Viz post seemed to predate the Lambda helper functions. Somewhat oddly, I did use the Binet formula to determine why my pseudo-SCAN failed with #NUM! errors long before I reached the 10,000th term you mentioned. I hit overflow errors somewhere before the 1,500th term which in turn meant that my formula returned nothing but the error.
The main takeaway for me from the exercise is that the inbuilt Excel functions SCAN, MAP, BYCOL, BYROW are all inadequately scoped since the nested array should be the basic building block of the functionality, not an error. As it stands, the use Lambda helper functions in formulas can be more restrictive than relying on the underlying array methods they support, since the lifting and broadcasting behaviours of the array formula are not generalised as one might hope.
PeterBartholomew1 not a problem at all, indeed your approach is very interesting too. I realized that the problem of numerical precision is affecting not just the Binet formula, but also the initial approach I provided in my post (formula 2). Check my reply to lori_m. Using the REDUCE approach starts to fail for getting the 74th Fibonacci number, even it involves just additions. Have you tested your formula for this number or greater than this one?, unless the Python algorithm is also wrong, but it involves just additions too.
I worked on the assumption that the integer calculation was exact until I reached 15 significant digits after which Excel does not report the desired precision so the numbers returned are only approximations. The approximation will become larger as one proceeds.
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.
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)
