Sucesión de Fibonacci en Excel y VBA

La sucesión de Fibonacci es una sucesión infinita en la que los dos primeros elementos son 0 y 1, y los siguientes términos son la suma de los dos anteriores:

0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377...

En Excel podemos generar la sucesión mediante fórmulas. Sumamos los dos primeros elementos y arrastramos el controlador de relleno.

Sin embargo, como el elemento 74, fib(74) = 1304969544928657, excede los 15 dígitos, Excel devuelve un número incorrecto: 1304969544928660.

Microsoft Excel admite un máximo de 15 dígitos significativos en todo momento. Este límite se aplica a un valor que se calcula mediante una fórmula. Debido a esta limitación, en cualquier momento una fórmula calcula un valor que supere los 15 dígitos de longitud, dígitos más allá el decimoquinto dígito significativo se cambian a ceros.

Función

Para resolver este comportamiento recurrimos a VBA. Traté con el código publicado en rosettacode.org. Pero fib(46) provoca un desbordamiento. Además, provocaría siempre el desbordamiento un término antes del máximo posible porque calcula el próximo nº Fibonacci un término antes de lo necesario, .

'Desbordamiento en fib(46)
Public Function Fib(n As Integer) As Long
    Dim fib0, fib1, sum As Long
    Dim i As Integer
    fib0 = 0
    fib1 = 1
    For i = 1 To n
        sum = fib0 + fib1 'Calcula nº siguiente antes de tiempo
        fib0 = fib1
        fib1 = sum
    Next
    Fib = fib0
End Function

Finalmente, ver notas abajo, modificando este código consigo calcular hasta fib(139) sin desbordamiento.

'Desbordamiento en fib(139)
Public Function Fibonacci(ByVal n As Long)
    Dim i As Long
    Dim a As Variant, b As Variant, tmp As Variant
    a = 0
    b = 1
    For i = 1 To n
        tmp = b
        b = a
        a = CDec(a + tmp) 'Conversión de Variant a Decimal
    Next
    Fibonacci = CStr(a) 'Mostrar todos los dígitos como texto
End Function

Subrutina

A continuación transformamos la función definida por el usuario (FDU) anterior en subrutina. En el cuadro de diálogo escribimos el número Fibonacci que queremos calcular.

'Desbordamiento en fib(139)
Sub Secuencia_Fibonacci()
    n = VBA.InputBox("Número de la serie Fibonacci a calcular. Máx. 139.")
    Dim i As Long
    Dim a As Variant, b As Variant, tmp As Variant
    a = 0
    b = 1
    For i = 1 To n
        tmp = b
        b = a
        a = CDec(a + tmp) 
    Next
    MsgBox "Fib(" & n & ")= " & a, vbInformation 
End Sub
End Sub

Notas

Necesitamos forzar la conversión del resultado a = CDec(a + tmp) con CDec de Variant a Decimal (28 dígitos). De esta manera, evitamos que VBA pierda la precisión necesaria y redondee a 15 dígitos a partir de fib(74) = 1,30496954492866E+15 en lugar de 1304969544928657 (16 dígitos). No podemos declarar las variables como Decimal directamente. Debemos definirlas como Variant y luego asignarles el tipo Decimal. Finalmente, convertimos el resultado de la función en texto con CStr para que Excel conserve todos los dígitos en las celdas y no vuelva redondear a 15 dígitos.

En cualquier caso, el término máximo de la sucesión Fibonacci que Excel puede calcular es el 139. Pues fib(140) = 81055900096023504197206408605 excede el valor máximo que Excel puede usar: 79228162514264337593543950335.

Referencias:

Límite de 15 dígitos.
Definir variable como Decimal.
Calculadora de sucesión de Fibonacci

Entradas relacionadas:
Sucesión de Fibonacci en R