How to compare column values and have Excel highlight the least value

[BrettOlbrys]

I created a spreadsheet of approximately 3,000 + rows of data (column A
- pricing information by product). The columns represent my different
customers price for that respective product (columns B - E).

If this is the following information:

A2 = Product A
B2 = $1.00
C2 = $1.50
D2 = $1.25
E2 = $2.00

What formula do I use to compare the cells (B2 - E2) so that Excel can
then return the lowest cost item in that row and highlight it or change
it's color, etc...?

Thanks

 

[Anders S]

One way is to use Conditional Formatting (see Excel Help) with a formula like

=(COLUMN()-1)=MATCH(MIN($B2:$E2),$B2:$E2,0)

and choose a suitable formatting style.

The formula will find the lowest value in columns B:E. If two or more values are
the same, the first fill be returned.

HTH
Anders Silvén

 

[Jason Morin]

1. Select your range of prices (A2:E3000).
2. Go to Format > Conditional Formatting.
3. Select "Formula Is" on the drop down arrow.
4. Enter this formula:
=B2=MIN($B2:$E2)
5. Click the Format button and format as desired.
6. Click OK twice.

HTH
Jason
Atlanta, GA

 

[BrettOlbrys]

I REALLY appreciate the help!

 

[BrettOlbrys]

Using the Conditional Formatting, Excel states:

"To use a formula as the formatting criteria (to evaluate data or a
condition other than the values in selected cells), click Formula Is
and then enter the formula that evaluates to a logical value of TRUE or
FALSE."

I don't want the resultant to be True or False, but rather the
resultant to be the lowest price. How do I evaluate a row of prices
and have the lowest price be displayed?

Thanks

Brett

 

[Andy B]

Brett

Have you tried the solutions given? Conditional formatting does need a TRUE
or FALSE, and the way the solutions posted work is by giving you just that!!

Andy.

 

[BrettOlbrys]

Yes, I tried the solutions suggested and even went to the Help file in
Excel, but it would not produce the result I was looking for.

In one case, the resultant was TRUE (in a parallel cell), in another
case, the entire row was highlighted. I simply want the lowest price
in the row to be highlighted and every forula I tried would not produce
the result.

Please explain, in the following formula what does the =B2 do?

=B2=MIN($B2:$E2)

And in this formula, what do I need to input where it says column()?

=(COLUMN()-1)=MATCH(MIN($B2:$E2),$B2:$E2,0)

Thanks