approximate a value to other values

[el07aab]

Hello,

I have a table D28:D1628 with values incrementing up to 5 decimal values.
In a different cell I generate a number that is not always identical to the values in the D28:D1628 column, therefore I need to approximate this value to the nearest one of the column.

Does anyone have an idea how to do this ?

Many many thanks in advance

 

[Special-K99]

Nearest without going over that value or just the nearest?
Can't you use MATCH for the former and ROUND for the latter?

 

[el07aab]

I have to approximate the given value to one that is contained in the D28:D1628 table.

How do I use the match function to go to the nearest value ?

 

[Special-K99]

=INDEX(D28:D1628,MATCH(A1,D28:D1628,1),1)

where A1 is the number you are looking for
the difference in MATCH here is

MATCH(A1,D28:D1628,1)
which finds a value "less than" rather than the usual 0 "equals"

 

[el07aab]

thanks a lot for that!

the only (minor) thing now is that if the given value does not match the one in the given table it always gives the one LOWER to its nearest one, as you have already mentioned.

for example if the original value is 10.94637 and the ones in the table are 10.94680 and 10.93700, it automatically takes the latter one although this is not the one which is nearest to it.

Do you have an idea how to change this ?

 

[Special-K99]

There may be a better way but try

=IF(ABS(ABS(A1-INDEX(D28:D1628,MATCH(A1,D28:D1628,1),1))) < ABS(ABS(A1-INDEX(D28:D1628,MATCH(A1,D28:D1628,1)+1,1))),ABS(A1-INDEX(D28:D1628,MATCH(A1,D28:D1628,1),1)),ABS(A1-INDEX(D28:D1628,MATCH(A1,D28:D1628,1)+1,1)))