Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 variables-SOS | 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 |