How To Highlight the Closest Value to A Given Number in Excel
In this Intermediate excel tutorial, I’ll explain how you can find the closest value from a list to given number in excel. Suppose you care creating a cutoff score and you need to find score closest to 65. From an unordered list its quite difficult. But now we can not only find the closest number but also highlight it.
We are going to use conditional formatting to solve this and here is how you should do it.
Step 1: Select the number list from which you need to find the closest number.
Step 2: Under the “Home Tab” click on “Conditional Formatting” and then “New Rule”
Step 3: Now select “Use a formula to determine which cells to format”
Step 4: In the below box write the formula
=ABS(A2-$C$2)=MIN(ABS($A$2:$A$26-$C$2))
Step 5: Now click on “Format” and select “Fill” now select the highlight color.
Step 6: Now click ok Twice and Done.
From now on whenever you write a number in cell C2 it will find the closest number from the list in cell A2 to A26.
Now lets breakdown the formula a bit so that you can customize it to your needs.
First, we used ABS function and then Min Function. Abs function converts a number to its absolute value. It also converts negative number to positive number keeping the positive number intact. To learn about Min Function Check this Tutorial: [ Ссылка ]
To learn all about excel functions please check: [ Ссылка ]
Now in the formula Cell A2 is the first cell of our number list. A26 is the last cell of our number list. C2 is the cell where we provide our random number. Change these cell reference according to your need.
This is how we find and highlight the closest number from a list to a given number.
Thanks for watching.
----------------------------------------------------------------------------------------
Support the channel with as low as $5
[ Ссылка ]
----------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial
[ Ссылка ]
Here goes the most recent video of the channel:
[ Ссылка ]
Playlists:
Advance Excel Tutorial: [ Ссылка ]
Excel Tutorial for Beginners: [ Ссылка ]
Excel Case: [ Ссылка ]
Combine Workbook & Worksheets: [ Ссылка ]
All About Comments in Excel: [ Ссылка ]
Excel VBA Programming Course: [ Ссылка ]
Social media:
Facebook: [ Ссылка ]
Twitter: [ Ссылка ]
Blogger: [ Ссылка ]
Tumblr: [ Ссылка ]
Instagram: [ Ссылка ]
Hubpages: [ Ссылка ]
Quora: [ Ссылка ]
Website: [ Ссылка ] *Become a member and enjoy exclusive perks while supporting the channel you love!*
[ Ссылка ]
