Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 8th 08, 03:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 48
Default 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   Report Post  
Old February 8th 08, 06:25 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old February 8th 08, 06:45 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 48
Default 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   Report Post  
Old February 8th 08, 07:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old February 8th 08, 07:43 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 48
Default 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   Report Post  
Old February 8th 08, 07:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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   Report Post  
Old February 8th 08, 07:54 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 48
Default 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   Report Post  
Old February 8th 08, 08:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


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 variables-SOS 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


All times are GMT +1. The time now is 10:02 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017