excelroundingworksheet-functionfibonacci

In Excel, how to round to nearest fibonacci number

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

Solution

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