Home 
Search 
Today's Posts 
#1




Conditional 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 
#2




Conditional format and multiple variables
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 FormatConditional 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 FormatConditional Formatting Formula Is: =AND(K2<"",K2=$J2) Click the Format button Select the style(s) desired OK out  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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 
#3




Conditional format and multiple variables
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... "T. Valko" wrote: 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 FormatConditional 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 FormatConditional Formatting Formula Is: =AND(K2<"",K2=$J2) Click the Format button Select the style(s) desired OK out  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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 
#4




Conditional format and multiple variables
Ok, Try these:
J4 Formula Is: =AND(J4<"",J4=MIN(J4:R4)) K4:R4 Formula Is: =AND(K4<"",K4=MIN($J4:$R4))  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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... "T. Valko" wrote: 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 FormatConditional 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 FormatConditional Formatting Formula Is: =AND(K2<"",K2=$J2) Click the Format button Select the style(s) desired OK out  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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 
#5




Conditional format and multiple variables
THANK YOU so much...
Not only did this correct the current problem, but I now understand the syntax of these arguments... Again  much appreciation. "T. Valko" wrote: Ok, Try these: J4 Formula Is: =AND(J4<"",J4=MIN(J4:R4)) K4:R4 Formula Is: =AND(K4<"",K4=MIN($J4:$R4))  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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... "T. Valko" wrote: 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 FormatConditional 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 FormatConditional Formatting Formula Is: =AND(K2<"",K2=$J2) Click the Format button Select the style(s) desired OK out  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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 
#6




Conditional format and multiple variables
You're welcome. Thanks for the feedback!
 Biff Microsoft Excel MVP "jmcclain" wrote in message ... THANK YOU so much... Not only did this correct the current problem, but I now understand the syntax of these arguments... Again  much appreciation. "T. Valko" wrote: Ok, Try these: J4 Formula Is: =AND(J4<"",J4=MIN(J4:R4)) K4:R4 Formula Is: =AND(K4<"",K4=MIN($J4:$R4))  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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... "T. Valko" wrote: 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 FormatConditional 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 FormatConditional Formatting Formula Is: =AND(K2<"",K2=$J2) Click the Format button Select the style(s) desired OK out  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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 
#7




Conditional format and multiple variables
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? "T. Valko" wrote: You're welcome. Thanks for the feedback!  Biff Microsoft Excel MVP "jmcclain" wrote in message ... THANK YOU so much... Not only did this correct the current problem, but I now understand the syntax of these arguments... Again  much appreciation. "T. Valko" wrote: Ok, Try these: J4 Formula Is: =AND(J4<"",J4=MIN(J4:R4)) K4:R4 Formula Is: =AND(K4<"",K4=MIN($J4:$R4))  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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... "T. Valko" wrote: 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 FormatConditional 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 FormatConditional Formatting Formula Is: =AND(K2<"",K2=$J2) Click the Format button Select the style(s) desired OK out  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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 
#8




Conditional format and multiple variables
Try this:
J4 Formula Is: =AND(J4<"",COUNT(K4:R4)0,J4=MIN(J4:R4))  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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? "T. Valko" wrote: You're welcome. Thanks for the feedback!  Biff Microsoft Excel MVP "jmcclain" wrote in message ... THANK YOU so much... Not only did this correct the current problem, but I now understand the syntax of these arguments... Again  much appreciation. "T. Valko" wrote: Ok, Try these: J4 Formula Is: =AND(J4<"",J4=MIN(J4:R4)) K4:R4 Formula Is: =AND(K4<"",K4=MIN($J4:$R4))  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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... "T. Valko" wrote: 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 FormatConditional 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 FormatConditional Formatting Formula Is: =AND(K2<"",K2=$J2) Click the Format button Select the style(s) desired OK out  Biff Microsoft Excel MVP "jmcclain" wrote in message ... 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Multiple Conditional Format  Excel Discussion (Misc queries)  
Multiple variablesSOS  Excel Worksheet Functions  
Conditional Statement with many variables  Excel Discussion (Misc queries)  
Conditional formatting for more than 3 variables  Excel Worksheet Functions  
Is there an easy way calculate 2 variables in conditional sum wiz.  Excel Worksheet Functions 