Finding a closest match of a number coloring it (Conditional Formating)

[ahmetk]

Dear Excel Gurus,


Every 3 to 4 year I search second hand car dealers & internet to find good deals.


As always in order to find best prices I rely on excel


I have a simple method: Write down all potentials, divide total KM with price, find per KM price per car. And also do the same for the full total. And compare.


Please see the following link for details: http://speedy.sh/EdHT9/Finding-closest-match-coloring-it.xlsx


I want to put a "conditional formating" and color the closest figure to "AVG TL" under "TL per KM" coloum.


For instance with in my excel file for year 2011, the "AVG TL" is 1,09 TL so 1,35 TL under "TL per KM" should be colored, letting me know that the best price/value ratio is the car on row 13.


Under Chandoo forum I have found the following information and answers:


http://chandoo.org/forums/topic/finding-a-closest-match-of-a-number


But due to two reasons I couln't understand it.

1) I am using Turkish excel therefore none of the formulas posted on the forum really works for me with copy&paste. I have to translate each formula, thus sometimes resulting mistakes

2) related attached file was no longer avaliable to study.


Can this be done?


Kind regards.


Ahmet K.

 

[ahmetk]

Dear All,


I solved it, thank you.


{=INDEX(f7:f14,MATCH(MIN(ABS(f7:f14-g7)),ABS(f7:f14-g7),0))}


don't forget to Press and hold Ctrl + Shift.


Kind regards.


AK.