Fibonacci Numbers is a series of numbers where next number is found by adding two previous numbers. It starts with 0, followed by 1 and summing up 0 and 1 gives next number as 1. Now sum up previous two numbers 1 and 1 and result is is 2. Next number would be 1+2 = 3 and next would 2+3 =5 and so on..Hence, below are Fibonacci Numbers.
0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89…
A good introduction to Fibonacci Numbers is found at
http://www.mathsisfun.com/numbers/fibonacci-sequence.html
The series can be generated very easily by putting 0 in A1 and 1 in A2 and a formula =A1+A2 in A3 which can be dragged down to produce this series.
But it requires a formula to be put into A3 which is not a good way for Excel users like you. In Excel, our endeavour always have to be to find a formula which can be put in first cell and can be dragged down.
Now, the challenge for you is to find that formula which can be put in A1 and dragged down to generate this Fibonacci Sequence.
Note – You may post the answer in the comments section.
=IF(ROW(A1)=1,0,IF(ROW(A1)=2,1,SUM(OFFSET(A1,-2,0),OFFSET(A1,-1,0))))
I know this reply is many years after the fact so my provided solution may not be accurate based on available functions in 2014 but, you solution must change based on the assumed start of the Fibonacci Sequence. Some sources give "0, 1, 1, 2, 3, 5, 8, ….", others give "1, 1, 2, 3, 5, 8, …", and still others give "1, 2, 3, 5, 8, …".
As such, you can generate the three different sequences using the following:
"0,1"=IFERROR(OFFSET(A1,-1,0)+IFERROR(OFFSET(A1,-2,0),1),0)
"1,1"=IFERROR(OFFSET(A1,-2,0)+OFFSET(A1,-1,0),1)
"1,2"=IFERROR(OFFSET(A1,-1,0)+IFERROR(OFFSET(A1,-2,0),1),1)
I know this challenge was for a formula, but numbers in Excel have a limited length, but if we use a VBA function, we can go much higher than a formula can. Note that in my remarks section, I speak about times for my "fast computer"… this code and its remarks were written some 15 years ago or so, so I would think times on modern computers will be much faster.
' This is an "infinite" precision calculator which prints a list of the
' full Nth Fibonacci numbers up to virtually any maximum Fibonacci Number,
' limited only to the maximum number of digits that a string can hold. Be
' aware, however, this routine slows down as the inputted number gets larger;
' for example, my fairly fast computer and it took 7.8 seconds for it to
' calculate the 2090 digits for the number 9999 and 12.6 minutes to calculate
' the 20,899 digits for the Fibonacci Number 99999, so if you plan to print
' out lists for larger numbers, be forewarned it could take awhile to do so.
Function FN(ByVal N As Long) As String
Dim X As Long, Z As Long, Carry As Long, PositionSum As Long
Dim N_minus_0 As String, N_minus_1 As String, N_minus_2 As String
If N = 1 Or N = 2 Then
FN = 1
Else
N_minus_1 = "1"
N_minus_2 = "1"
For X = 3 To N
Carry = 0
N_minus_0 = Space$(Len(N_minus_1))
If Len(N_minus_1) > Len(N_minus_2) Then N_minus_2 = "0" & N_minus_2
For Z = Len(N_minus_1) To 1 Step -1
PositionSum = Val(Mid$(N_minus_1, Z, 1)) + Val(Mid$(N_minus_2, Z, 1)) + Carry
Mid$(N_minus_0, Z, 1) = Right$(CStr(PositionSum), 1)
Carry = IIf(PositionSum < 10, 0, 1)
Next
If Carry Then N_minus_0 = "1" & N_minus_0
N_minus_2 = N_minus_1
N_minus_1 = N_minus_0
Next
FN = N_minus_0
End If
End Function
Thanks Rick, this is a good alternative.