If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 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  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 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 > >  > 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 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 >> >>  >> 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 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 > >> > >>  > >> 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 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 >> >> >> >>  >> >> 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 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 > >> >> > >> >>  > >> >> 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 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 >> >> >> >> >> >>  >> >> >> 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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Multiple Conditional Format  Duncs  Excel Discussion (Misc queries)  10  July 24th 07 12:01 PM 
Multiple variablesSOS  Ang  Excel Worksheet Functions  3  April 27th 07 08:24 PM 
Conditional Statement with many variables  Krista  Excel Discussion (Misc queries)  3  May 12th 06 10:58 PM 
Conditional formatting for more than 3 variables  Emile  Excel Worksheet Functions  8  March 7th 06 04:27 PM 
Is there an easy way calculate 2 variables in conditional sum wiz.  Liketoknow  Excel Worksheet Functions  1  November 10th 04 09:34 AM 