approximate a value to other values

el07aab

New Member
Joined
Jul 20, 2010
Messages
8
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Nearest without going over that value or just the nearest?
Can't you use MATCH for the former and ROUND for the latter?
 

el07aab

New Member
Joined
Jul 20, 2010
Messages
8
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
=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

New Member
Joined
Jul 20, 2010
Messages
8
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,035
Messages
5,622,332
Members
415,894
Latest member
silverhaze

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top