In Excel, how to round to nearest fibonacci number

Question

In Excel, I would like to round to the nearest fibonacci number.

I tried something like (sorry with a french Excel):

RECHERCHEH(C7;FIBO;1;VRAI) -- HLOOKUP(C7, FIBO, 1, TRUE)

where FIBO is a named range (0; 0,5; 1;2;3;5;8;etc.)

my problem is that this function rounds to the smallest number and not the nearest. For example 12.8 is rounded to 8 and not 13.

Note: I just want to use an excel formula, and no VBA

Answer 1

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)))

Answer 2

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.