Highlight Lowest value ignoring 0 or blank cell

[Marie]

I have 4 adjacent columns of values. For each row, I need to highlight the
lowest value, ignoring any blank or 0 value cells in that row. There are
about 1200 rows.......so I need this to repeat to the end of the rows.

Any takers?

As always, thanks in advance for your kind help and input.

 

[Peo Sjoblom]

Select the top row, do format>conditional formatting,
formula is and use

=A2=SMALL($A$2:$D$2,COUNTIF($A$2:$D$2,0)+1)

click the format button and select either pattern or font
and change the colour. Copy using format painter to the rest of the cells

Adapt to fit your own cell ranges


--


Regards,


Peo Sjoblom

 

[Marie]

Top row formula is:
=B3=SMALL($B$3:$E$3,COUNTIF($B$3:$E$3,0)+1)
next row copied via format painter or by copy/paste special - format either
one is:
=B4=SMALL($B$3:$E$3,COUNTIF($B$3:$E$3,0)+1)

As you can see, formula adjusts in first cell reference for row change but
does not adjust for balance of formula. What am I doing wrong?

Marie

 

[Marie]

OK..... figured it out and am posting the answer so others will be able to
use it.

As with everything, it's setting up the first row that is important but you
can't use the wildcard $ sign in the formula as it prohibits the adatation of
the formula as you go down the rows. So.... solution. Each cell has to be
'conditionally formatted' by itself.

1st cell 1 row
=B3=SMALL(B3:E3,COUNTIF(B3:E3,0)+1)
2nd cell 1 row
=C3=SMALL(B3:E3,COUNTIF(B3:E3,0)+1)
3rd cell 1 row
=D3=SMALL(B3:E3,COUNTIF(B3:E3,0)+1)
4th cell 1 row
=E3=SMALL(B3:E3,COUNTIF(B3:E3,0)+1)

Now you can select the entire row and format copy them down the whole
column. This works, I did it.

Thanks for all the help and good luck with future endeavors.

Marie