Conditional formatting to highlight the closest value

[craig oxo]

My scenario:

I have a row of a range of values across 10 columns, on the 11th column is my actual figure, or the data that is appropriate

say:

100,90,80,70,60,50,40,30,20,10 - my figure being 37

I need the cell containing '40' to highlight without any other cell changing, and i just cant get my head around it!!!

Submitted via EggHeadCafe
SharePoint Tip / Thought of the Day WebPart
http://www.eggheadcafe.com/tutorial...arepoint-tip--thought-of-the-day-webpart.aspx

 

[joeu2004]

I have a row of a range of values across 10 columns,
on the 11th column is my actual figure, or the data
that is appropriate say:
100,90,80,70,60,50,40,30,20,10 - my figure being 37
I need the cell containing '40' to highlight without
any other cell changing

For the example given, assuming 100,...,10 are in A1:J1 and 37 is in
K1, then select A1:J1 and enter the following Conditional Formatting
formula ("Formula Is"):

=ROUND($K$1,-1)=A1

then select the desired CF Format.

Note that this CF formula works only if A1:J1 are multiples of 10. Do
you want a formula that will work any arbitrary values in A1:J1?

Also note that if A1>=105 or A1<5, nothing will be highlighted. Do
you want a CF formula that will highlight the appropriate endpoint in
that case?