12

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

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Cédric
  • 123
  • 1
  • 4

3 Answers3

7

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)))
Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
  • 1
    Wow ! Thanks. Translated in french Excel for those who might be interested : =INDEX(FIBO;1; SI(C7>=(INDEX(FIBO;1;(EQUIV(C7;FIBO;1)))+INDEX(FIBO;1;(EQUIV(C7;FIBO;1)+1)))/2; EQUIV(C7;FIBO;1)+1; EQUIV(C7;FIBO;1))) – Cédric Jun 29 '11 at 09:02
  • 1
    Thanks! Translated in spanish Excel: =INDICE(FIBO;1; SI(B3>=(INDICE(FIBO;1;(COINCIDIR(B3;FIBO;1)))+INDICE(FIBO;1;(COINCIDIR(B3;FIBO;1)+1)))/2; COINCIDIR(B3;FIBO;1)+1; COINCIDIR(B3;FIBO;1))) FIBO is a named range in horizontal cells – nacho Jun 19 '19 at 10:17
  • Thanks! Translated in portuguese Excel: `=ÍNDICE(FIBO,1,SE(C7>=(ÍNDICE(FIBO,1,(CORRESP(C7,FIBO,1)))+ÍNDICE(FIBO,1,(CORRESP(C7,FIBO,1)+1)))/2,CORRESP(C7,FIBO,1)+1,CORRESP(C7,FIBO,1)))` – Fernando Silveira Oct 10 '19 at 18:46
  • 1
    Thanks for this! I did something similar but I only wanted to round up, which actually makes the formula simpler (but it does require reverse-sorting your range of point values): `=INDEX(FIBO, 1, MATCH(C7, FIBO, -1))` – bryant Nov 21 '19 at 19:57
  • Thanks! Translated in german Excel: =INDEX(FIBO;1; WENN(B15>=(INDEX(FIBO;1;(VERGLEICH(B15;FIBO;1)))+INDEX(FIBO;1;(VERGLEICH(B15;FIBO;1)+1)))/2; VERGLEICH(B15;FIBO;1)+1; VERGLEICH(B15;FIBO;1))) – Lusk116 Apr 22 '21 at 12:46
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.

RonnieDickson
  • 1,400
  • 10
  • 9
0

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))))))))
Sylhare
  • 5,907
  • 8
  • 64
  • 80
Alfred Weil
  • 1