In Excel, how to round to nearest fibonacci number

user_819495 0
Viewed 4661

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

worksheet-function fibonacci rounding excel
3 Answers

I used a simpler nested IF solution.

I calculated the mid point between each pair of Fibonacci numbers and used that as the decision point. The following tests the value in A2 to produce the desired Fibonacci number:

=IF(A2>=30,40,IF(A2>=16.5,20,IF(A2>=10.5,13,IF(A2>=6.5,8,IF(A2>=4,5,IF(A2>=2.5,3,IF(A2>=1.5,2,IF(A2>=0.5,1,0))))))))
user_129560050
Related