Conditional format and multiple variables

[jmcclain]

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

 

[T. Valko]

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

 

[jmcclain]

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]

Ok, Try these:

J4
Formula Is:
=AND(J4<>"",J4=MIN(J4:R4))

K4:R4
Formula Is:
=AND(K4<>"",K4=MIN($J4:$R4))

 

[jmcclain]

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]

You're welcome. Thanks for the feedback!

 

[jmcclain]

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]

Try this:

J4
Formula Is:
=AND(J4<>"",COUNT(K4:R4)>0,J4=MIN(J4:R4))