PC Review


Reply
Thread Tools Rate Thread

Conditional formatting to highlight the closest value

 
 
craig oxo
Guest
Posts: n/a
 
      25th Feb 2011
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/tutorials...y-webpart.aspx
 
Reply With Quote
 
 
 
 
joeu2004
Guest
Posts: n/a
 
      25th Feb 2011
On Feb 25, 1:33*am, craig oxo <oxo...@hotmail.com> wrote:
> 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?
 
Reply With Quote
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting - to highlight overwritten formulas JohnG Microsoft Excel Worksheet Functions 13 22nd May 2010 12:33 AM
Conditional formatting - highlight an entire row Dave K Microsoft Excel Discussion 1 22nd Aug 2008 10:39 PM
Highlight text - Can not use conditional formatting. JKlein Microsoft Access Form Coding 3 21st Jun 2006 03:00 PM
How do I highlight a cell using conditional formatting and dates =?Utf-8?B?c2hhbmU1NjE=?= Microsoft Excel Worksheet Functions 6 27th Dec 2005 03:56 AM
conditional formatting to highlight top score eddy_eagle Microsoft Excel Misc 2 21st Dec 2003 10:48 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:38 AM.