In Excel, how to round to nearest fibonacci number

This will work:

=INDEX(FIBO,1, IF(C7>=(INDEX(FIBO,1,(MATCH(C7,FIBO,1)))+
    INDEX(FIBO,1,(MATCH(C7,FIBO,1)+1)))/2, MATCH(C7,FIBO,1)+1, MATCH(C7,FIBO,1)))

Define the target number Targ, relative to which we want to find the closest Fib number.

Define

n = INT(LN(Targ*SQRT(5))/LN((1+SQRT(5))/2))

It follows that Fib(n) <= Targ <= Fib(n+1)

where one can compute Fib(n) and Fib(n+1) via

Fib(n) = ROUND(((1+SQRT(5))/2)^n/SQRT(5),0)

Finally find the closest Fib number to Targ using the computed values of Fib(n) and Fin(n+1).

Not as compact as the other solution presented since it requires a few helper formulas, but it requires no table for Fib numbers.

Related