FIBONACCI

=FIBONACCI(n)

n
integer, the nth term of the sequence

calculates the nth term of a Fibonacci sequence

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
FIBONACCI !! recursive !! calculates the nth term of the famous Fibonacci sequence . This is more of a fun quest than a useful function. ( did this on a YT channel also, had fun with Rico about this).
This reflects how limited our computers are. You will be surprised. This time is not about recursion "iterations" limit, is about the limit of the number of calculations that a computer can perform.
Open a new workbook, define the lambda FIBONACCI. Very important, first, try values of n less then 20!! . Call FIBONACC(15) ...(19) ..etc. and increase it bit by bit.
For what number "n" do you think that your computer will start struggling giving you the answer?? Share with us here, if you please, how long it took your computer to calculate FIBONACCI(35)..(38) and (40). Eventually what type of computer you are using. Processor ,RAM etc. After all, 40 is a very small number.? Give it a try!!
Note: Fibonacci sequence is this one : 1,1,2,3,5,8,13,21,34,55,89,144....the value of n term is the sum of the previous 2 terms fib( n)=fib(n-1) + fib(n-2), and fib(1)=1,fib(2)=1
Fortunately , Fibonacci has also an explicit solution (non recursive), you can define another lambda to check the results FIB( n)=LAMBDA( n,LET(a,SQRT(5),x,((1+a)/2)^n,y,((1-a)/2)^n,1/a*(x-y)))
Excel Formula:
=LAMBDA(n,IF(n<3,1,FIBBONACCI(n-1)+FIBONACCI(n-2)))
LAMBDA 6.0.xlsx
ABCDEFGH
1FIBONACCIrecursiveckeck(non recursive)
2n value=FIBONACCI(B3)=FIB(B3)time(can use phone's stopwatch)
315610610instant
41825842584instant
52067656765instant
6308320408320401s
73592274659227465?sshare this
8383908816939088169?svalues
940102334155102334155?swith us ?
10
FIBONACCI post
Cell Formulas
RangeFormula
C2:D2C2=FORMULATEXT(C3)
C3:C9C3=FIBONACCI(B3)
D3:D9D3=FIB(B3)
 
Upvote 0
Lots of chatter here, but no mention that the LAMBDA posted has a typo. Posted is
"=LAMBDA(n,IF(n<3,1,FIBBONACCI(n-1)+FIBONACCI(n-2)))", but for any value 3 or more it just generates a #NAME! error. Should be
Excel Formula:
=LAMBDA(n,IF(n<3,1,FIBONACCI(n-1)+FIBONACCI(n-2)))
Just sayin'.
Indeed, a good point, and the explanation is quite simple. The formula is so short that everybody typed it and they did it correctly, they did not copy mine, like you did. And when we type typos are possible. ✌️😉🙏
Anyhow, in the minisheet all the functions are fine. ✌️
Hope that Fibonacci(10000) calculated here FIBONACCI has all the digits ok. 🤣
 
Momentous Day Today!! Happy 25th anniversary mrexcel.com !!🎉🎉
Congratulations to @MrExcel, to @smozgur and to all who made this amazing cutting-edge Best in The World, One of a kind, Second to None, Excel Laboratory, possible.
As a late "participant" I have realized that this is not only a place where problems get solved, it's more than that, it's about passion. And I am proud and grateful to be part of it among all the other pasionate people.
I thought that the best way to celebrate is to post something special to me, from my unpublished studies, something cool enough for this special day, something that reflects the passion, and here it is, an updated way to calculate Fibonacci nr. , replacing recursive design with an iterative reduce with array accumulator, simpler and lot faster than before.
Main function is a function designed to add exceptionally large numbers (numbers formatted as text, to keep the precision)
LADD(a) Large nr. ADDing
a: any array of large text numbers or regular numbers, positive integers
(The study has functions for all operations with large nr. : adding, multiplication, subtraction, dividing, sorting, comparing etc...but for FIB LADD is enough
Excel Formula:
=LAMBDA(a,
    LET(
        m, LZ(a, 1),
        n, COLUMNS(m),
        d, REDUCE(
            {0, ""},
            SEQUENCE(n, , n, -1),
            LAMBDA(v, i, LET(x, INDEX(m, , i), s, SUM(x) + INDEX(v, 1), g, LEN(s), l, IF(g = 1, 0, LEFT(s, g - 1)), r, RIGHT(s), HSTACK(l, r & INDEX(v, 2))))
        ),
        XZ(CONCAT(d))
    )
)
calls 2 tiny tool functions LZ(a,[e]) Leading 0's and XZ(a) eXclude leading 0's
a: array
e: expand arg., if omitted strings, if 1 expanded with MID
Excel Formula:
=LAMBDA(a, [e], LET(l, LEN(a), n, MAX(l), z, REPT(0, n - l) & a, m, --MID(TOCOL(z), SEQUENCE(, n), 1), IF(e, m, z)))
Excel Formula:
=LAMBDA(a, MAP(a, LAMBDA(x, IFERROR(REPLACE(x, 1, MIN(TOCOL(SEARCH(VSTACK("-", SEQUENCE(9)), x), 2)) - 1, ""), "0"))))
and FIB(n ) that calls LADD(a)
Excel Formula:
=LAMBDA(n,
    TAKE(REDUCE({0; 1}, SEQUENCE(n - 1), LAMBDA(v, i, VSTACK(INDEX(v, 2), LADD(VSTACK(INDEX(v, 1), INDEX(v, 2)))))), -1)
)
Other functions on mini-sheet
WS(s,c) Wrap String s: string to wrap; c: nr. chars /row
Excel Formula:
=LAMBDA(s, c, MID(s, SEQUENCE(ROUNDUP(LEN(s) / c, 0), , , c), c))
25 years.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1LZ(a,[e]) and XZ(a) LADD(a) funcionality
2
398765399876543456543123
40
5
6=LZ(B3:C4)=LZ(B3:C4,1)
7987653998765434565430000000000000000012398765399876543456543
8000000000000000000000000000000000000000000000000000000000123
900000000000000000000
1000000000000000000000
11
12=XZ(B7#)
1398765399876543456543123
1400
15
16=LADD(B7#)
1798765399876543456666
18Adiing 500000 numbers of 20 digits each
19
20=IF(SEQUENCE(250000,2),REPT(9,20))=LADD(B21#)
21999999999999999999999999999999999999999949999999999999999999500000
229999999999999999999999999999999999999999
239999999999999999999999999999999999999999
Sheet1
Cell Formulas
RangeFormula
E6,E20,B20,B16,B12,B6E6=FORMULATEXT(E7)
B7:C8B7=LZ(B3:C4)
E7:X10E7=LZ(B3:C4,1)
B13:C14B13=XZ(B7#)
B17B17=LADD(B7#)
B21:C250020B21=IF(SEQUENCE(250000,2),REPT(9,20))
E21E21=LADD(B21#)
Dynamic array formulas.
 
If with previous method FIB(5000) took 18min now is less than 1min to calculate!!!
wolfram alpha link to check values fib(10000) - Wolfram|Alpha
Book1
BCD
1
2fib nr
3100043466557686937456435688527675040625802564660517371780402481729089536555417949051890403879840079255169295922593080322634775209689623239873322471161642996440906533187938298969649928516003704476137795166849228875
4
5
620004224696333392304878706725602341482782579852840250681098010280137314308584370130707224123599639141511088446087538909603607640194711643596029271983312598737326253555802606991585915229492453904998722256795316982874482472992263901833716778060607011615497886719879858311468870876264597369086722884023654422295243347964480139515349562972087652656069529806499841977448720155612802665404554171717881930324025204312082516817125
7
8
9422469633339230487870672560234148278257985284025068109801028013731430858437013070722412359963914151108844608753890960360
10764019471164359602927198331259873732625355580260699158591522949245390499872225679531698287448247299226390183371677806060
11701161549788671987985831146887087626459736908672288402365442229524334796448013951534956297208765265606952980649984197744
128720155612802665404554171717881930324025204312082516817125
13
14
153000410615886307971260333568378719267105220125108637369252408885430926905584274113403731330491660850044560830036835706942274588569362145476502674373045446852160486606292497360503469773453733196887405847255290082049086907512622059054542195889758031109222670849274793859539133318371244795543147611073276240066737934085191731810993201706776838934766764778739502174470268627820918553842225858306408301661862900358266857238210235802504351951472997919676524004784236376453347268364152648346245840573214241419937917242918602639810097866942392015404620153818671425739835074851396421139982713640679581178458198658692285968043243656709796000
16
17
18410615886307971260333568378719267105220125108637369252408885430926905584274113403731330491660850044560830036835706942274
19588569362145476502674373045446852160486606292497360503469773453733196887405847255290082049086907512622059054542195889758
20031109222670849274793859539133318371244795543147611073276240066737934085191731810993201706776838934766764778739502174470
21268627820918553842225858306408301661862900358266857238210235802504351951472997919676524004784236376453347268364152648346
22245840573214241419937917242918602639810097866942392015404620153818671425739835074851396421139982713640679581178458198658
23692285968043243656709796000
24
25
26400039909473435004422792081248094960912600792570982820257852628876326523051818641373433549136769424132442293969306537520118273879628025443235370362250955435654171592897966790864814458223141914272590897468472180370639695334449662650312874735560926298246249404168309064214351044459077749425236777660809226095151852052781352975449482565838369809183771787439660825140502824343131911711296392457138867486593923544177893735428602238212249156564631452507658603400012003685322984838488962351492632577755354452904049241294565662519417235020049873873878602731379207893212335423484873469083054556329894167262818692599815209582517277965059068235543139459375028276851221435815957374273143824422909416395375178739268544368126894240979135322176080374780998010657710775625856041594078495411724236560242597759185543824798332467919613598667003025993715274875
27
28
29399094734350044227920812480949609126007925709828202578526288763265230518186413734335491367694241324422939693065375201182
30738796280254432353703622509554356541715928979667908648144582231419142725908974684721803706396953344496626503128747355609
31262982462494041683090642143510444590777494252367776608092260951518520527813529754494825658383698091837717874396608251405
32028243431319117112963924571388674865939235441778937354286022382122491565646314525076586034000120036853229848384889623514
33926325777553544529040492412945656625194172350200498738738786027313792078932123354234848734690830545563298941672628186925
34998152095825172779650590682355431394593750282768512214358159573742731438244229094163953751787392685443681268942409791353
3522176080374780998010657710775625856041594078495411724236560242597759185543824798332467919613598667003025993715274875
36
37
3850003878968454388325633701916308325905312082127714646245106160597214895550139044037097010822916462210669479293452858882973813483102008954982940361430156911478938364216563944106910214505634133706558656238254656700712525929903854933813928836378347518908762970712033337052923107693008518093849801803847813996748881765554653788291644268912980384613778969021502293082475666346224923071883324803280375039130352903304505842701147635242270210934637699104006714174883298422891491273104054328753298044273676822977244987749874555691907703880637046832794811358973739993110106219308149018570815397854379195305617510761053075688783766033667355445258844886241619210553457493675897849027988234351023599844663934853256411952221859563060475364645470760330902420806382584929156452876291575759142343809142302917491088984155209854432486594079793571316841692868039545309545388698114665082066862897420639323438488465240988742395873801976993820317174208932265468879364002630797780058759129671389634214252579116872755600360311370547754724604639987588046985178408674382863125
39
40
41387896845438832563370191630832590531208212771464624510616059721489555013904403709701082291646221066947929345285888297381
42348310200895498294036143015691147893836421656394410691021450563413370655865623825465670071252592990385493381392883637834
43751890876297071203333705292310769300851809384980180384781399674888176555465378829164426891298038461377896902150229308247
44566634622492307188332480328037503913035290330450584270114763524227021093463769910400671417488329842289149127310405432875
45329804427367682297724498774987455569190770388063704683279481135897373999311010621930814901857081539785437919530561751076
46105307568878376603366735544525884488624161921055345749367589784902798823435102359984466393485325641195222185956306047536
47464547076033090242080638258492915645287629157575914234380914230291749108898415520985443248659407979357131684169286803954
48530954538869811466508206686289742063932343848846524098874239587380197699382031717420893226546887936400263079778005875912
499671389634214252579116872755600360311370547754724604639987588046985178408674382863125
50
51
Sheet1
Cell Formulas
RangeFormula
D3D3=FIB(1000)
D6D6=FIB(2000)
D9:D12,D41:D49,D29:D35,D18:D23D9=WS(D6,120)
D15D15=FIB(3000)
D26D26=FIB(4000)
D38D38=FIB(5000)
Dynamic array formulas.

Book1
BCD
1
2fib nr
31000033644764876431783266621612005107543310302148460680063906564769974680081442166662368155595513633734025582065332680836159373734790483865268263040892463056431887354544369559827491606602099884183933864652731300088830269235673613135117579297437854413752130520504347701602264758318906527890855154366159582987279682987510631200575428783453215515103870818298969791613127856265033195487140214287532698187962046936097879900350962302291026368131493195275630227837628441540360584402572114334961180023091208287046088923962328835461505776583271252546093591128203925285393434620904245248929403901706233888991085841065183173360437470737908552631764325733993712871937587746897479926305837065742830161637408969178426378624212835258112820516370298089332099905707920064367426202389783111470054074998459250360633560933883831923386783056136435351892133279732908133732642652633989763922723407882928177953580570993691049175470808931841056146322338217465637321248226383092103297701648054726243842374862411453093812206564914032751086643394517512161526545361333111314042436854805106765843493523836959653428071768775328348234345557366719731392746273629108210679280784718035329131176778924659089938635459327894523777674406192240337638674004021330343297496902028328145933418826817683893072003634795623117103101291953169794607632737589253530772552375943788434504067715555779056450443016640119462580972216729758615026968443146952034614932291105970676243268515992834709891284706740862008587135016260312071903172086094081298321581077282076353186624611278245537208532365305775956430072517744315051539600905168603220349163222640885248852433158051534849622434848299380905070483482449327453732624567755879089187190803662058009594743150052402532709746995318770724376825907419939632265984147498193609285223945039707165443156421328157688908058783183404917434556270520223564846495196112460268313970975069382648706613264507665074611512677522748621598642530711298441182622661057163515069260029861704945425047491378115154139941550671256271197133252763631939606902895650288268608362241082050562430701794976171121233066073310059947366875
4
5
6336447648764317832666216120051075433103021484606800639065647699746800814421666623681555955136337340255820653326808361593
7737347904838652682630408924630564318873545443695598274916066020998841839338646527313000888302692356736131351175792974378
8544137521305205043477016022647583189065278908551543661595829872796829875106312005754287834532155151038708182989697916131
9278562650331954871402142875326981879620469360978799003509623022910263681314931952756302278376284415403605844025721143349
10611800230912082870460889239623288354615057765832712525460935911282039252853934346209042452489294039017062338889910858410
11651831733604374707379085526317643257339937128719375877468974799263058370657428301616374089691784263786242128352581128205
12163702980893320999057079200643674262023897831114700540749984592503606335609338838319233867830561364353518921332797329081
13337326426526339897639227234078829281779535805709936910491754708089318410561463223382174656373212482263830921032977016480
14547262438423748624114530938122065649140327510866433945175121615265453613331113140424368548051067658434935238369596534280
15717687753283482343455573667197313927462736291082106792807847180353291311767789246590899386354593278945237776744061922403
16376386740040213303432974969020283281459334188268176838930720036347956231171031012919531697946076327375892535307725523759
17437884345040677155557790564504430166401194625809722167297586150269684431469520346149322911059706762432685159928347098912
18847067408620085871350162603120719031720860940812983215810772820763531866246112782455372085323653057759564300725177443150
19515396009051686032203491632226408852488524331580515348496224348482993809050704834824493274537326245677558790891871908036
20620580095947431500524025327097469953187707243768259074199396322659841474981936092852239450397071654431564213281576889080
21587831834049174345562705202235648464951961124602683139709750693826487066132645076650746115126775227486215986425307112984
22411826226610571635150692600298617049454250474913781151541399415506712562711971332527636319396069028956502882686083622410
2382050562430701794976171121233066073310059947366875
24
Sheet1
Cell Formulas
RangeFormula
D3D3=FIB(10000)
D6:D23D6=WS(D3,120)
Dynamic array formulas.
fib 10000.png
 
25 years.xlsx
BCD
1
2fib nr
3200002531162323732361242240155003520607291766356485802485278951929841991312781760541315230153423463758831637443488219211037689033673531462742885329724071555187618026931630449193158922771331642302030331971098689235780843478258502779200293635651897483309686042860996364443514558772156043691404155819572984971754278513112487985892718229593329483578531419148805380281624260900362993556916638613939977074685016188258584312329139526393558096840812970422952418558991855772306882442574855589237165219912238201311184749075137322987656049866305366913734924425822681338966507463855180236283582409861199212323835947891143765414913345008456022009455704210891637791911265475167769704477334859109822590053774932978465651023851447920601310106288957894301592502061560528131203072778677491443420921822590709910448617329156135355464620891788459566081572824889514296350670950824208245170667601726417091127999999941149913010424532046881958285409468463211897582215075436515584016297874572183907949257286261608612401379639484713101138120404671732190451327881433201025184027541696124114463488665359385870910331476156665889459832092710304159637019707297988417848767011085425271875588008671422491434005115288334343837778792282383576736341414410248994081564830202363820504190074504566612515965134665683289356188727549463732830075811851574961558669278847363279870595320099844676879457196432535973357128305390290471349480258751812890314779723508104229525161740643984423978659638233074463100366500571977234508464710078102581304823235436518145074482824812996511614161933313389889630935320139507075992100561077534028207257574257706278201308302642634678112591091843082665721697117838726431766741158743554298864560993255547608496686850185804659790217122426535133253371422250684486113457341827911625517128815447325958547912113242367201990672230681308819195941016156001961954700241576553750737681552256845421159386858399433450045903975167084252876848848085910156941603293424067793097271128806817514906531652407763118308162377033463203514657531210413149191213595455280387631030665594589183601575340027172997222489081631144728873621805528648768511368948639522975539046995395707688938978847084621586473529546678958226255042389998718141303055036060772003887773038422366913820397748550793178167220193346017430024134496141145991896227741842515718997898627269918236920453493946658273870473264523119133765447653295022886429174942653014656521909469613184983671431465934965489425515981067546087342348350724207583544436107294087637975025147846254526938442435644928231027868701394819091132912397475713787593612758364812687556725146456646878912169274219209708166678668152184941578590201953144030519381922273252666652671717526318606676754556170379350956342095455612780202199922615392785572481747913435560866995432578680971243966868110016581395696310922519803685837460795358384618017215468122880442252343684547233668502313239328352671318130604247460452134121833305284398726438573787798499612760939462427922917659263046333084007208056631996856315539698234022953452211505675629153637867252695056925345220084020071611220575700841268302638995272842160994219632684575364180160991884885091858259996299627148614456696661412745040519981575543804847463997422326563897043803732970397488471644906183310144691243649149542394691524972023935190633672827306116525712882959108434211652465621144702015336657459532134026915214509960877430595844287585350290234547564574848753110281101545931547225811763441710217452979668178025286460158324658852904105792472468108996135476637212057508192176910900422826969523438985332067597093454021924077101784215936539638808624420121459718286059401823614213214326004270471752802725625810953787713898846144256909835116371235019527013180204030167601567064268573820697948868982630904164685161783088076506964317303709708574052747204405282785965604677674192569851918643651835755242670293612851920696732320545562286110332140065912751551110134916256237884844001366366654055079721985816714803952429301558096968202261698837096090377863017797020488044826628817462866854321356787305635653577619877987998113667928954840972022833505708587561902023411398915823487627297968947621416912816367516125096563705174220460639857683971213093125
4
5len
64180253116232373236124224015500352060729176635648580248527895192984199131278176054131523015342346375883163744348821921103768
7903367353146274288532972407155518761802693163044919315892277133164230203033197109868923578084347825850277920029363565189
8748330968604286099636444351455877215604369140415581957298497175427851311248798589271822959332948357853141914880538028162
9426090036299355691663861393997707468501618825858431232913952639355809684081297042295241855899185577230688244257485558923
10716521991223820131118474907513732298765604986630536691373492442582268133896650746385518023628358240986119921232383594789
11114376541491334500845602200945570421089163779191126547516776970447733485910982259005377493297846565102385144792060131010
12628895789430159250206156052813120307277867749144342092182259070991044861732915613535546462089178845956608157282488951429
13635067095082420824517066760172641709112799999994114991301042453204688195828540946846321189758221507543651558401629787457
14218390794925728626160861240137963948471310113812040467173219045132788143320102518402754169612411446348866535938587091033
15147615666588945983209271030415963701970729798841784876701108542527187558800867142249143400511528833434383777879228238357
16673634141441024899408156483020236382050419007450456661251596513466568328935618872754946373283007581185157496155866927884
17736327987059532009984467687945719643253597335712830539029047134948025875181289031477972350810422952516174064398442397865
18963823307446310036650057197723450846471007810258130482323543651814507448282481299651161416193331338988963093532013950707
19599210056107753402820725757425770627820130830264263467811259109184308266572169711783872643176674115874355429886456099325
20554760849668685018580465979021712242653513325337142225068448611345734182791162551712881544732595854791211324236720199067
21223068130881919594101615600196195470024157655375073768155225684542115938685839943345004590397516708425287684884808591015
22694160329342406779309727112880681751490653165240776311830816237703346320351465753121041314919121359545528038763103066559
23458918360157534002717299722248908163114472887362180552864876851136894863952297553904699539570768893897884708462158647352
24954667895822625504238999871814130305503606077200388777303842236691382039774855079317816722019334601743002413449614114599
25189622774184251571899789862726991823692045349394665827387047326452311913376544765329502288642917494265301465652190946961
26318498367143146593496548942551598106754608734234835072420758354443610729408763797502514784625452693844243564492823102786
27870139481909113291239747571378759361275836481268755672514645664687891216927421920970816667866815218494157859020195314403
28051938192227325266665267171752631860667675455617037935095634209545561278020219992261539278557248174791343556086699543257
29868097124396686811001658139569631092251980368583746079535838461801721546812288044225234368454723366850231323932835267131
30813060424746045213412183330528439872643857378779849961276093946242792291765926304633308400720805663199685631553969823402
31295345221150567562915363786725269505692534522008402007161122057570084126830263899527284216099421963268457536418016099188
32488509185825999629962714861445669666141274504051998157554380484746399742232656389704380373297039748847164490618331014469
33124364914954239469152497202393519063367282730611652571288295910843421165246562114470201533665745953213402691521450996087
34743059584428758535029023454756457484875311028110154593154722581176344171021745297966817802528646015832465885290410579247
35246810899613547663721205750819217691090042282696952343898533206759709345402192407710178421593653963880862442012145971828
36605940182361421321432600427047175280272562581095378771389884614425690983511637123501952701318020403016760156706426857382
37069794886898263090416468516178308807650696431730370970857405274720440528278596560467767419256985191864365183575524267029
38361285192069673232054556228611033214006591275155111013491625623788484400136636665405507972198581671480395242930155809696
39820226169883709609037786301779702048804482662881746286685432135678730563565357761987798799811366792895484097202283350570
408587561902023411398915823487627297968947621416912816367516125096563705174220460639857683971213093125
41
Sheet2
Cell Formulas
RangeFormula
D3D3=FIB(20000)
B6B6=LEN(D3)
D6:D40D6=WS(D3,120)
Dynamic array formulas.

fib 20000.png
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top