Finding the lowest price in a row

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Is there a formula that will look at the numbers I have
listed in a row and find the lowest one and format it
differently then the rest of my worksheet by
hightlighting it or making it bold?
 
1. Select the row (let's assume row 2).
2. Go to Format > Conditional Formatting
3. Select Formula Is and insert:
=MIN(2:2)=A2
4. Press the Format button and format as desired.

HTH
Jason
Atlanta, GA
 
AFAIK you would have to use a macro or a macro converted to a UDF (user
defined function which is a function you create)
 
Hi Don
Conditional format should be able to do this :-)
For the OP:
- if your data is in A1:A100 try the following:
- select A1:A100
- goto 'Format - Conditional Format'
- Enter the formula
=A1=MIN($A$1:$A$100)
- choose a format

Note: if two or more values are equal to the minimum value
ALL are highlighted
 
Hi Jason
Thank you for your quick reply, however, when I do what
you have instructed excel is highlighting my entire row
with the exception of AE4 (which is not the lowest
number).
The cells containing my data are from H4 to AE4 so I have
first highlighted across only these cells and then
followed your steps, inserted like this =MIN(4:4)=A4.
What am I doing wrong?
 
Hi

Yes, that must be my problem, because 2 of my cells equal
$0.00. Now my question is how do I use this conditional
format and tell it to exclude cells that equal $0.00?
any suggestions?
 
Hi Jason
Thank you! That seemed to work however, I neglected to
mention 2 of my cells have a $0.00 in them and excel is
highlighting only those 2 cells. Is there a way I can
use that same formula and tell it to exclude cells that
have $0.00 in them?
Karen
 
Ignoring 0's requires a different formula. Use this:

=SMALL($H$4:$AE$4,COUNTIF($H$4:$AE$4,0)+1)=H$4

Jason
 
Hi
in this case youneed a different formula. Try
=SMALL($A$1:$A$100,COUNTIF($A$1:$A$100,0)+1)=A1
 
Hi Jason

I'm sorry to be such a pest, but I've copied this formula
you gave me and for the first time it actually did only
format the one cell. The only problem is it didn't find
the lowest number. There were two cells (besides the
ones with "0" in them) that were lower. I've examined
the formula and everything seems to be correct so any
ideas why this might be?
 
Send me your file and I'll take a look at it. Put "Cond
Format" in the subject line.

Jason
 
Hi
if you could also have negative values try the following
formula:
=SMALL($A$1:$A$100,--(MIN($A$1:$A$100)=0)*COUNTIF
($A$1:$A$100,0)+1)=A1
 
Back
Top