 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Conditional format and multiple variables
I have a simple spreadsheet with a value in column J (our price). Columns K through R are the prices our competitors charge for the item. I need to compare column J to the others and shade J if the price is less than or equal to any of the values in K through R. In addition, I need to shade any value in K through R if it equals J. One possible hitch, column J is driven by column F (formula in J is "=f4) etc.. Any help would be appreciated. I have read the sections in my Excel 2003 guide on conditional formatting, but not having much luck. Thanks, Jon >shade J if the price is less than or equal to >any of the values in K through R. Are you sure you didn't mean less than or equal to *all* of the other values? Here's how to do it for *any*: Assume your data is in the range J2:R10 Select the range J2:J10 Goto the menu Format>Conditional Formatting Formula Is: =COUNTIF(K2:R2,">="&J2)>0 Click the Format button Select the style(s) desired OK out Select the range K2:R10 Goto the menu Format>Conditional Formatting Formula Is: =AND(K2<>"",K2=$J2) Click the Format button Select the style(s) desired OK out  SignatureBiff Microsoft Excel MVP >I have a simple spreadsheet with a value in column J (our price). Columns >K [quoted text clipped - 14 lines] > > Jon Thanks very much - but it isn't working exactly as I need. Assuming data range is k4:R4, I need it to only shade the lowest value(s) if the value is less than the value in J4. If no value in the range is <= to K4, then shade K4. Any help is appreciated... > >shade J if the price is less than or equal to > >any of the values in K through R. [quoted text clipped - 38 lines] > > > > Jon Ok, Try these: J4 Formula Is: =AND(J4<>"",J4=MIN(J4:R4)) K4:R4 Formula Is: =AND(K4<>"",K4=MIN($J4:$R4)) SignatureBiff Microsoft Excel MVP > Thanks very much - but it isn't working exactly as I need. > [quoted text clipped - 51 lines] >> > >> > Jon THANK YOU so much... Not only did this correct the current problem, but I now understand the syntax of these arguments... Again - much appreciation. > Ok, Try these: > [quoted text clipped - 61 lines] > >> > > >> > Jon You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > THANK YOU so much... > [quoted text clipped - 73 lines] >> >> > >> >> > Jon I hate to bug you again, but how can I make the value in J not shade if there are no values in k:R? > You're welcome. Thanks for the feedback! > [quoted text clipped - 75 lines] > >> >> > > >> >> > Jon Try this: J4 Formula Is: =AND(J4<>"",COUNT(K4:R4)>0,J4=MIN(J4:R4)) SignatureBiff Microsoft Excel MVP >I hate to bug you again, but how can I make the value in J not shade if >there [quoted text clipped - 85 lines] >> >> >> > >> >> >> > Jon |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.