Fibonacci Sequence in Excel--A Quick Exercise

[thesilentpyro]

A quick contest: see what the highest number is in the Fibonacci Sequence that Excel can calculate.

Bonus points if you get the un-truncated version (Excel cuts it off an uses scientific notation). Note: if you post it this way, put spaces in it every once in a while so the page does not stretch.

[Buttons]

If you want the largest one that Excel can calculate accurately, it doesn't seem to be very big. 806,515,533,049,393.

[crazyjimbo]

If you want the largest one that Excel can calculate accurately, it doesn't seem to be very big. 806,515,533,049,393.

OpenOffice Calc concurs.

[evilbeanfiend]

hand on! do you want the highest factorial that can be accurately returned by
=fact(x) in a single cell, or the highest you could get an excel spreadsheet to calculate? cos i'm pretty sure you could write a sheet to get the next one up by breaking the multiplication into smaller parts and then concatenating the results together as a string (with logic to handle overflows at the breaks). hell, if you are allowed to use VBA then you are probably only limited by available memory

[thesilentpyro]

Sorry, should've specified: not accurately--at all. Eventually it gets to the point where the numbers are only in scientific notation, and it goes upwards off 255 digits (the max one cell can hold), eventually to the point where Excel will not allow any bigger numbers. And no, no VBA allowed (I did think of that one) just cell equations.

[Yakk]

Couldn't you cheat? Use cells as your digits, and do carries manually?

I guess that doesn't give you much better performance than using a single cell: you'd only gain 5 or so orders of magnitude.

[zenten]

This compelled me to write a little python program to calculate the fibonacci sequence. I set it up as a shell script, and have it dumped into a text file. Started it about 10 minutes ago, already at 120 MB

[evilbeanfiend]

Couldn't you cheat? Use cells as your digits, and do carries manually?

I guess that doesn't give you much better performance than using a single cell: you'd only gain 5 or so orders of magnitude.

if you fill every cell in with a string of digits and go over multiple sheets in one workbook i think you are only limited by available memory (assuming there is no upper limit on the number of sheets in a workbook) but i suspect this counts as cheating.

[thesilentpyro]

Okay, further specifications: one cell.

Really, I'm just curious. I don't know why, I have no rational reasoning--maybe it's just a distraction from the mindless data entry I'm supposed to be doing (intern). I set up a spreadsheet to do it myself, but it won't let me see the three-hundred-something digits I need to see, and I don't feel like setting something up in VBA to do it, as that would take up more time than I care to take out of my work.

[Buttons]

To see the whole number, just change the format from "general" to "number". You'll notice, though, that after the first fifteen digits, it just prints zeroes.

[thesilentpyro]

Nope for the biggest one it can calculate, I get a whole line of number signs (#), indicating the number is too large to fit in the cell. Expanding the cell as wide as possible (255 characters) does nothing to change this.

[evilbeanfiend]

To see the whole number, just change the format from "general" to "number". You'll notice, though, that after the first fifteen digits, it just prints zeroes.

its display precision is probably limited to double precision numbers which stop at 14-15 decimal places for most systems (not sure if there are any versions for hardware with large precision doubles).

[skeptical scientist]

You could get increased accuracy by using an absolute rather than a recursive formula for the numbers, so errors won't propagate.

[Buttons]

You could get increased accuracy by using an absolute rather than a recursive formula for the numbers, so errors won't propagate. :P

Although you'd probably still get a good amount of error, since you'd be taking approximations of irrationals to very large powers. But meh. I've never really found computer arithmetic very interesting anyway.

[skeptical scientist]

True. You could do it symbolically and avoid any inaccuracies though, although not in Excel.

[3.14159265...]

Can we use the closed form to calculate?

[cmacis]

Can you get a good enough approximation to sqrt(5)?

[Durinthal]

Basic way of doing it (A1 = A2 = 1, A3=A1+A2, copy that formula from then on out) comes up with this: 1.3069892237634E+308

That's just shy of 2^1024, which is the upper end of the Double data type (largest number) in Excel.

Oh, and that's F(1476), too.

[3.14159265...]

Can you get a good enough approximation to sqrt(5)?

I didn't actually go to do it, so missed that point

[thesilentpyro]

Basic way of doing it (A1 = A2 = 1, A3=A1+A2, copy that formula from then on out) comes up with this: 1.3069892237634E+308

That's just shy of 2^1024, which is the upper end of the Double data type (largest number) in Excel.

Oh, and that's F(1476), too.

Aye, that's the number I found. Now the tricky part: What are those 309 digits?

[Buttons]

If you insist.

Code: Select all

13069 89223 76339 93180 36311 55380 27198 30983 92443 90741
26407 26006 65946 01927 93070 47923 17402 88681 08777 70177
21095 46315 49790 12276 23432 22469 36939 64718 53667 06368
48936 26608 44147 44994 13484 62800 92275 58189 69634 74334
89829 16424 95406 27441 35969 86561 54072 76492 41065 37217
74590 66954 48014 90837 64916 17320 95972 65806 46300 33793
34717 1632

Courtesy of an especially large file. Sorta funny that the most accurate source I could find on large fibonacci numbers is also stuffed with such new-age flim-flammery as

Especially the fibonacci-numbers with their strong connection to nature you can look at them as a prayer build by the names (invocation) of the names of the numbers making up the sequence.

Um, yeah.

[Blatm]

That site is weird.

I cannot write about this yet, but I feel Prime-Numbers are living beings, not as we imagine them as deceased beings for a form and ideas of being human, but they, as all numbers, are conscious beings; and there is a way to address Prime-Numbers in an affirmation and discover their hidden nature which has not been yet discover or cover by the solely mathematical approach. As soon I find reference material or by my own experience I will include this here on this page.

Also,

Pi also is considered a transcendental number (a mathematical term), it means it cannot be expressed by a ratio of two integers.

Why do I get the impression the author isn't a math major?

[thesilentpyro]

If you insist.

Code: Select all

13069 89223 76339 93180 36311 55380 27198 30983 92443 90741
26407 26006 65946 01927 93070 47923 17402 88681 08777 70177
21095 46315 49790 12276 23432 22469 36939 64718 53667 06368
48936 26608 44147 44994 13484 62800 92275 58189 69634 74334
89829 16424 95406 27441 35969 86561 54072 76492 41065 37217
74590 66954 48014 90837 64916 17320 95972 65806 46300 33793
34717 1632

Thanks much. Not that this was a serious pursuit. Now to get Excel to display this thing...

[Buttons]

hilarious quotes

Man, that's amazing. "Spiritual" people crack me up. Sorta reminds me of

This flame, like all flames, represents the light and darkness. It also represents the uncertainty of life and its delicacy. It also represents a penis.

[Xial]

I was washing my dishes and just started to clean up all spoons, forks and knifes which were left.

So, I was starting to hear within me:

"Greetings, we are 13"

Hmm ....

"We are 13"

No, there are more spoons, forks and knifes, at least 17 or even more I thought. Sometimes I know the amount of things I look at without actually counting them, but this time it was different.

"We are 13"

No, please, I'm already enganged with a lot of mundane stuff, I can't stand now a spirit pretending being 13, or saying there are 13 spoons, knifes and so forth. "Stop it" I thought and hoped that presence or spirit, or whoever it was to stop.

"Count us"

Anyone else want to join a religion where you get to talk with utensils? I guarantee it involves at least one type of illicit substance.

[evilbeanfiend]

True. You could do it symbolically and avoid any inaccuracies though, although not in Excel.

why not? excel is turing equivalent even without VBA (assuming number of sheets in a work book isn't bounded, otherwise its a FSM).

[skeptical scientist]

Well, I meant "not in excel" meaning "not if you want to just say 'evaluate this expression symbolically'". Of course you could do some strange cell coding to program your spreadsheet to do the symbolic evaluation, but that would be rather stupid since you'd be much better off using something that already has symbolic calculation built in.

[Yakk]

True. You could do it symbolically and avoid any inaccuracies though, although not in Excel.

why not? excel is turing equivalent even without VBA (assuming number of sheets in a work book isn't bounded, otherwise its a FSM).

How do you loop? (or unbounded recurse)

I assume there is some kind of "write to location X,Y" function we can use?