A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Conditional format and multiple variables



 
 
Thread Tools Display Modes
  #1  
Old February 8th 08, 04:33 PM posted to microsoft.public.excel.worksheet.functions
jmcclain
external usenet poster
 
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
Ads
  #2  
Old February 8th 08, 07:25 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
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 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  
Old February 8th 08, 07:45 PM posted to microsoft.public.excel.worksheet.functions
jmcclain
external usenet poster
 
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 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  
Old February 8th 08, 08:26 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
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 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  
Old February 8th 08, 08:43 PM posted to microsoft.public.excel.worksheet.functions
jmcclain
external usenet poster
 
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 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  
Old February 8th 08, 08:46 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
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 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  
Old February 8th 08, 08:54 PM posted to microsoft.public.excel.worksheet.functions
jmcclain
external usenet poster
 
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 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  
Old February 8th 08, 09:03 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
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 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 05:27 PM
Is there an easy way calculate 2 variables in conditional sum wiz. Liketoknow Excel Worksheet Functions 1 November 10th 04 10:34 AM


All times are GMT +1. The time now is 01:24 AM.


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