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?
Aladin Any ideas?
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)
Aladin
Posted by David on December 19, 2001 5:38 AM
If you want a quick way of manually checking, use Find & replace, replace it with ZZ099, and then replace it back again. The first time you do it should show you how many replacements were made. Is there a quick way to count the number of times a particular formula occurs in a worksheet. I am using an Excel add-in and it would really help to know how many times a particular function appears in a worksheet, but I fear I am treading into some deep VBA waters.
