# approximate a value to other values

#### el07aab

##### New Member
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 ?

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Nearest without going over that value or just the nearest?
Can't you use MATCH for the former and ROUND for the latter?

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 ?

=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"

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 ?

There may be a better way but try

Code:
``=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)))``

Replies
6
Views
867
Replies
9
Views
369
Replies
4
Views
116
Replies
2
Views
102
Replies
3
Views
166

1,211,841
Messages
6,104,302
Members
447,901
Latest member
boy3hc2004

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back