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

  • Thread starter Thread starter BrettOlbrys
  • Start date Start date
B

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
 
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
 
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
 
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
 
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.
 
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
 
Back
Top