ExcelBanter - Shading or Choosing closest

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Shading or Choosing closest (https://www.excelbanter.com/excel-worksheet-functions/50901-shading-choosing-closest.html)

Shading or Choosing closest

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%

--
lacosta
------------------------------------------------------------------------
lacosta's Profile: http://www.excelforum.com/member.php...o&userid=15519
View this thread: http://www.excelforum.com/showthread...hreadid=476926

Shading or Choosing closest

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 FormatConditional 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" wrote in
message ...

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%

--
lacosta
------------------------------------------------------------------------
lacosta's Profile:
http://www.excelforum.com/member.php...o&userid=15519
View this thread: http://www.excelforum.com/showthread...hreadid=476926

Shading or Choosing closest

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.

--
lacosta
------------------------------------------------------------------------
lacosta's Profile: http://www.excelforum.com/member.php...o&userid=15519
View this thread: http://www.excelforum.com/showthread...hreadid=476926

Shading or Choosing closest

Hi!

It works for me.

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

Biff

"lacosta" wrote in
message ...

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.

--
lacosta
------------------------------------------------------------------------
lacosta's Profile:
http://www.excelforum.com/member.php...o&userid=15519
View this thread: http://www.excelforum.com/showthread...hreadid=476926

Shading or Choosing closest