Using Conditional Format

[Guest]

I'm trying to highlight the cell with the lowest value by using Conditional
Format.
Example..
A3 = 4
b3 = 7
c3 = 3
d3 = 10
I want to highlight C3 but I tried
=MIN(K$3,$BN$3,$BQ$3,$BT$3) in the Formula Is part of Conditional Formating
and it highlights all the values. Can This be done.

TIA Again
Joe

 

[Steve]

Click on A3

Format, Conditional formatting, Formula is: '=A3 = MIN($A$3:$D$3)'
{without the single quotes)
Select your formatting options, OK

Copy cell A3
Paste special, formats over the rest of the range.

HTH
Steve

 

[Bob Phillips]

or even select A33, do the CF stuff, and save the copy paste afterwards.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you. It works great until I ran into one problem, when 0 is in the
equation it wont highlight anything. Is there a way to make it highlight
greater than zero?

TIA Again
Joe

 

[Biff]

Hi!

If 0 was the lowest value it should have highlighted that.

Formula is:

=A3=MIN(IF($A$3:$D$3>0,$A$3:$D$3))

Biff

 

[Steve]

Hmmmm, That's a bit more complex.

You need a minimum if statement. Excel doesn't provide one. So you'll need
to enter an array formula in a spare cell:

Copy the following to A1 (or somewhere else)

=MIN(IF(A33 >0,A33,"")) in a spare cell

Press Ctrl/Shift/Enter to enter the formula

Highlight A3: D3

Format, Conditional format,
Cell value is, equal to, enter '$A$1' (or your other cell reference) in
the third textbox

Click OK

Steve

 

[Guest]

Biff,
The formula I need is something like this since the values I'm evaluating
aren't side by side, they're every 3 columns.
=$BN3=MIN(IF(OR($BK3,$BN3,$BQ3,$BT3,$BW3)>0,OR($BK3,$BN3,$BQ3,$BT3,$BW3)))
My example does show them side by side but I didn't plan on running into
zeros.
Can this be done. with the formula like above it doesn't highlight anything.
Sorry for the wrong example.
Joe

 

[RagDyer]

This works for me:

Click in BN3, and start the conditional format.

Formula Is:
=BN3=MIN($BN$3,$BK$3,$BQ$3,$BT$3,$BW$3)

Then, set your format, and <OK> out.

Now, with BN3 still selected, *double click* on the "Format Painter" icon
(yellow paint brush), and navigate to, and click on, each of the cells in
the formula.
Then hit <Esc>.

And you're done!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Biff,
The formula I need is something like this since the values I'm evaluating
aren't side by side, they're every 3 columns.
=$BN3=MIN(IF(OR($BK3,$BN3,$BQ3,$BT3,$BW3)>0,OR($BK3,$BN3,$BQ3,$BT3,$BW3)))
My example does show them side by side but I didn't plan on running into
zeros.
Can this be done. with the formula like above it doesn't highlight anything.
Sorry for the wrong example.
Joe

 

[Guest]

That was the first attempt I made and it works except that it selects zero
when it's the lowest. I'm trying to get this to work by ignoring or using
greater than zero values and selecting the lowest value. Sometimes all cells
have values and it works fine, it's the zeros that foul things up.

TIA
Joe

 

[Biff]

Hi!

This is a real hack but it works:

Formula is:

=BK3=MIN(IF(MOD(COLUMN($BK$3:$BW$3)-COLUMN(OFFSET
($BK$3:$BW$3,,,1,1)),3)=0,IF($BK$3:$BW$3>0,$BK$3:$BW$3)))

You'll have to enter the cf in each cell individually. In
other words, you can not select cells BK3,BN3,BQ3,BT3 and
BW3 as a range and then enter the cf and expect the cell
references to automatically adjust.

You CAN enter the cf in one cell then Copy>Paste
Special>Formats to the other cells.

Biff

 

[Biff]

Hi!

That can be shortened to: (and it's not such a hack after
all!)

=BK3=MIN(IF(MOD(COLUMN($BK$3:$BW$3),3)=0,IF
($BK$3:$BW$3>0,$BK$3:$BW$3)))

Biff

 

[Guest]

Thank you, that worked perfectly, I needed to remove the $ before the 3's so
when I copied it down the row changed but it does work perfect. How did you
figure it out? What does MOD and -COLUMN do?

Again thank you
Joe

 

[Biff]

Hi!

Use the shortened version of the formula!

How it works:

Even though you see column headers as letters, when
referenced in functions columns evaluate as numbers.

=COLUMN(A:A) evaluates to =COLUMN(1)
=COLUMN(B:B) evaluates to =COLUMN(2)
etc..

The range of columns where the data is located is:

=COLUMN(BK:BW) or =COLUMN(63:75)

63 and 75 happen to be divisable by 3.

The MOD function returns the remainder that would result
in a division operation. 63 is divisable by 3 so there
would be no remainder, or:

=MOD(63,3) = 0

So, in the formula we want the columns in the range BK:BW
or 63:75 where the MOD = 0

So we end up with this array:

MOD(COLUMN(BK)=0 TRUE
MOD(COLUMN(BL)=0 FALSE
MOD(COLUMN(BM)=0 FALSE
MOD(COLUMN(BN)=0 TRUE
...
MOD(COLUMN(BW)=0 TRUE

This is what we get in plain English:

The value in cell X is the minimum if the cell in the
column has a MOD of zero when divided by 3 and the value
in the cell is greater than zero.

Not my best explanation but I hope it helps!

Biff

 

[Guest]

Thanks.

Hi!

Use the shortened version of the formula!

How it works:

Even though you see column headers as letters, when
referenced in functions columns evaluate as numbers.

=COLUMN(A:A) evaluates to =COLUMN(1)
=COLUMN(B:B) evaluates to =COLUMN(2)
etc..

The range of columns where the data is located is:

=COLUMN(BK:BW) or =COLUMN(63:75)

63 and 75 happen to be divisable by 3.

The MOD function returns the remainder that would result
in a division operation. 63 is divisable by 3 so there
would be no remainder, or:

=MOD(63,3) = 0

So, in the formula we want the columns in the range BK:BW
or 63:75 where the MOD = 0

So we end up with this array:

MOD(COLUMN(BK)=0 TRUE
MOD(COLUMN(BL)=0 FALSE
MOD(COLUMN(BM)=0 FALSE
MOD(COLUMN(BN)=0 TRUE
...
MOD(COLUMN(BW)=0 TRUE

This is what we get in plain English:

The value in cell X is the minimum if the cell in the
column has a MOD of zero when divided by 3 and the value
in the cell is greater than zero.

Not my best explanation but I hope it helps!

Biff