Comparing three columns, Conditional Formatting
 

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

Comparing three columns, Conditional Formatting
 

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
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Marsh" wrote in message
...
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

Comparing three columns, Conditional Formatting
 

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))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marsh" wrote:

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

Comparing three columns, Conditional Formatting
 

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.

"Luke M" wrote:

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))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marsh" wrote:

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

Comparing three columns, Conditional Formatting
 

Have you tried
=M5=MIN(IF($M5:$R5<0,$M5:$R5))
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Marsh" wrote in message
...
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.

"Luke M" wrote:

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))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Marsh" wrote:

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