Conditional Formatting

[Joe Gieder]

First thank you for your help.
I'm trying to use conditional formatting to highlight the cell that is the
lowest among these cells and I tried using this formula.
=$BM16=MIN(IF($BK16,BM16,BO16,$BQ16>0,$BK16,BM16,BO16,$BQ16))
I get an error because I guess you cannot use every other cell in the formula.
I cannot use a range because each alternating cell has a dollar value while
the other cells represent weeks from 1 - 52.

An example of the data is:
BK BL BM BN BO BP BQ BR
$11.340 1 $7.700 2 $10.722 2 $6.000 39

I'm trying to get this result:
The cell that should be highlighted is BQ16

If I use =$BM16=MIN(IF($BK16:$BR16>0,$BK16:$BR16)) it works but highlights
the wrong cell, it would highlight BL16.

Again thank you for your help
Joe

 

[Shane Devenshire]

Hi,

Try using this in your conditional formatting:

=BM16=MIN(IF((MOD(COLUMN(BK16:BQ16),2)=1)*(BK16:BQ16)>0,BK16:BQ16,""))

You original formula worries me because you have $ in front of the first and
last cells $BK and $BQ but not the other ones? I modified that a little. If
you are applying the formula down a single column then no need for the $ in
front of the references. If you are applying down a range then probably all
the column references need $ but not the rows.

 

[Joe Gieder]

Hi Shane,
It works perfect thank you. I just noticed one problem though with my
values, and that is in some of the rows there is text, example is

BK BL BM BN BO BP BQ BR
$6.640 6 $0.000 0 $0.000 0 NO BID

nothing gets highlighted. Is there a way to ignore text?

Thank you again
Joe