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

Lightweight

Why do you have 0.5 as a Fibonacci number? – Glenine

Well, this is a pseudo fibonacci list used by agilists to estimate user stories weight. It also includes 20 40 and 100 (or infinite) – Debt

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

Glenine

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

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 – Psychoneurotic

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

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

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

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.

Resistant

What about lucas numbers? What would that formula look like? – Stace

@JonLennartAasenden You can abuse the special property of the Lucas numbers to get any term after the first two: =ROUND(((1+SQRT(5))/2)^SEQUENCE(20,,2),0) will list the 20 terms after the first two. – Luhe

Answer 3

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

Zeb

Answer 4

Maybe even simpler than the nested IF method (which is my favorite):

=IF(T5>13,21,IF(T5>8,13,IF(T5>5,8,IF(T5>3,5,IF(T5>2,3,IF(T5>1,2,IF(T5>=0.5,1,0)))))))

(T5 is my cell reference, instead of A2 for this example)

Mostly just > is needed, not >=

This one maxes out at 21.

Flynn