# Excel - find closest number ?

Posted by David on December 18, 2001 10:53 AM

I would like excel to highlight the number (from a range of cells) that is closest to a specific cell.

Example: Several sales people have weekly goals. If the company goal is 100 (in it's own cell) and salesperson #1 has 99 and Salesperson #2 has 98, then highlight the 100.

Any ideas?

Posted by Aladin Akyurek on December 18, 2001 11:14 AM

David --

This appears to me a clear problem statement, which is then vcontradicted by the example you give. BTW, the example makes less sense to me than the problem statement. Which one is it that you want?

Posted by David on December 18, 2001 12:35 PM

Sorry for the confusion. I was trying to make it simple by making up an example that made more sense. Guess it didn't huh? I run a weekly contest to see who can guess the company's weekly sales. Whoever gets the closest wins the prize. I want to keep a spreadsheet of everyone's guesses and have it tell me who got the closest - without having to compare all the numbers.

Posted by Aladin Akyurek on December 18, 2001 12:57 PM

David --

It's OK. There is no need to restart another on the same question however.

You can do it with conditional formatting, as you thought yourself.

Lets say that you have the names in column A from A2 on.
Their guesses in B from B2 on.
The weekly sales which must be guessed in C2.
The figure that defines the closest in C3.

Select all of the cells in B from B2 on.
Activate Format|conditional Formatting.
Choose "Formula Is" for Condition 1.
Enter as formula:

=OR(B2=\$C\$2, ABS(B2-\$C\$2)=\$C\$3)

Activate Format.
Select one of the tabs.
Select the format you want.
Click OK.
Click OK.

Note. The formula looks at absolute difference. If you rather have a percentage difference in C3, say, .05, use the following formula instead:

=OR(B2=\$C\$2, 1-(B2/\$C\$2)=\$C\$3)