Shading or Choosing closest

[lacosta]

Hello All,

I need help with the following. I need Excel to highlight the closest
number to 100% in column C(Act. Price). At the same time once it finds
the closest thing in column C it need to highlight everything accross.
In this case the closest this to 100% is 100.14 therefore 8, 5.625% and
100.14% should be highlighted.

Thanks.


A B C
Rank Rate Act. Price
15 6.500% 103.95%
14 6.375% 103.21%
13 6.250% 102.48%
12 6.250% 102.48%
11 6.000% 101.94%
10 5.875% 101.18%
9 5.750% 100.43%
8 5.625% 100.14%
7 5.500% 99.50%
6 5.375% 98.72%
5 5.250% 97.40%

 

[Biff]

Hi!

Assume your data is in the range A1:C12

A1:C1 are the headers: Rank Rate Act. Price

Select the range A2:C12

Goto Format>Conditional Formatting
Formula is: =ABS($C2-1)=MIN(ABS($C$2:$C$12-1))
Click the Format button
Select the style(s) you want
OK out

Biff

 

[lacosta]

Biff,

Thanks for the reply but that did not work. MIN formula highlights the
minimum of the percentages and it doesn't highlight what's closes to
100%. In this case it is 100.14. Please help with this. Thank you.

 

[Biff]

Hi!

It works for me.

Want to see a sample file where it works? Just let me know where to send it.

Biff

 

[lacosta]

Biff,

please send to (e-mail address removed)

Thanks

 

[Biff]

Sample file on it's way.

Biff