Comparing three columns, Conditional Formatting

  • Thread starter Thread starter Marsh
  • Start date Start date
M

Marsh

In Excel 2007, I have a worksheet with dollar amounts in columns M, N, and R.
These values start in row 5 and go down to 12,456.
I need to pick out the lowest value in each row and give it a color (green
for example). Some of the cells in these ranges are blank, and those cells
with a null value should not be considered the lowest amount.
I have been stumbling around attempting to use conditional formatting, to no
avail.
Please help so I do not need to do this manually.

Many, many thanks
Marsh
 
You do not tell us what is in columns O,P, and Q. If it is text (or empty)
this will work:

In L5 I have used this formula to find the minimum values in the row
=MIN(IF(M5:R5<>0,M5:R5))
Note that this is an array formula so it must be entered using
Ctrl+Shift+Enter not just Enter.
This worked so I deleted the stuff in L and proceeded to conditional
formatting

I selected M5:R20 (you will need to select more) and entered this for the
conditional formatting rule
=M5=MIN(IF(M5:R5<>0,M5:R5)) and gave the cells a green fill if this was true

NOTE: no need to use Ctrl+Shift+Enter as Excel treats all conditional
formatting rules as array formulas (isn't that neat!)

best wishes
 
pergaps something like this as your CF formula, inputted into M5:

=M5=MIN(IF(ISNUMBER($M5),$M5),IF(ISNUMBER($N5),$N5),IF(ISNUMBER($R5),$R5))
 
Works well, except it is formatting null cells. Those must not be formatted.
If m16 is 25, N16 is empty and R16 is 11, cell R16 should be the one
formatted.
 
Back
Top