D
Dr. Darrell
If I have a colunm of numbers, can I conditionally format to highlite the
smallest value in the colunm?
smallest value in the colunm?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
If I have a colunm of numbers, can I format to highlite the smalle
B
Biff
Try this:
Assume your numbers are in the range A1:A5
Select the range A1:A5
Goto Format>Conditional Formatting
Formula Is: =AND(A1<>"",A1=MIN(A$1:A$5))
Click the Format button
Select the style(s) desired
OK out
Biff
Assume your numbers are in the range A1:A5
Select the range A1:A5
Goto Format>Conditional Formatting
Formula Is: =AND(A1<>"",A1=MIN(A$1:A$5))
Click the Format button
Select the style(s) desired
OK out
Biff
B
Bob Phillips
Yes, use a formula of
=A1=MIN(A:A)
=A1=MIN(A:A)
D
Dr. Darrell
Bob:
Thanks for your reply. This didn't work for me. My Range is actually J6:J22
so I substituted:
Does it make a difference if the values in the cells are dirived from a
formular,
<<< =IF(D6=0,0,((C6/(D6*2))+G6)) >>>
Darrell
Thanks for your reply. This didn't work for me. My Range is actually J6:J22
so I substituted:
=J6=MIN(J6:J22)
Does it make a difference if the values in the cells are dirived from a
formular,
<<< =IF(D6=0,0,((C6/(D6*2))+G6)) >>>
Darrell
D
Dr. Darrell
Biff:
Thanks for your reply. This didn't work for me. My Range is actually J6:J22
so I substituted:
=AND(J6<>"",J6=MIN(J$6:J$22))
Does it make a difference if the values in the cells are dirived from a
formular,
<<< =IF(D6=0,0,((C6/(D6*2))+G6)) >>>
Darrell
Thanks for your reply. This didn't work for me. My Range is actually J6:J22
so I substituted:
=AND(J6<>"",J6=MIN(J$6:J$22))
Does it make a difference if the values in the cells are dirived from a
formular,
<<< =IF(D6=0,0,((C6/(D6*2))+G6)) >>>
Darrell
R
Roger Govier
Hi
Did you do as Biff said and SELECT the range first?
In your case select J6:J22, then apply the Conditional Formatting with
Biff's formula.
Did you do as Biff said and SELECT the range first?
In your case select J6:J22, then apply the Conditional Formatting with
Biff's formula.
D
Dr. Darrell
Roger:
Sometimes I need to be slapped across the head with a 2 x 4.
Yes indeed, I needed to highlite the cells first. That did work.
Sometimes I need to be slapped across the head with a 2 x 4.
Yes indeed, I needed to highlite the cells first. That did work.
D
Dr. Darrell
Roger and Biff:
It appears that that isn't exactly what I wanted to do. Instead of the Range
being J6:J22 I actually need to use a group of cells. J6 & J29 & J53 & J77
Should I use the same formula and just deliniate my cells with a method
other than a colon?
Darrell
It appears that that isn't exactly what I wanted to do. Instead of the Range
being J6:J22 I actually need to use a group of cells. J6 & J29 & J53 & J77
Should I use the same formula and just deliniate my cells with a method
other than a colon?
Darrell
R
Roger Govier
Yes
=AND(J6<>"",J6=MIN($J$6,$J2$9,$J$53,$J$57))
=AND(J6<>"",J6=MIN($J$6,$J2$9,$J$53,$J$57))
D
Dr. Darrell
Roger:
That was very helpful. If you don't mind, may I continue with a few more
questions?
Can I copy the Conditional Formating from the group of cells I just formated
to other similar groups of cells i.e. (J7,J30,J54,J78) &(I6,I29,I53,I77)...
This should probably be done as a Macro since I have to do this 20 or more
times, but I'm not particularly adept at VBA.
Darrell
That was very helpful. If you don't mind, may I continue with a few more
questions?
Can I copy the Conditional Formating from the group of cells I just formated
to other similar groups of cells i.e. (J7,J30,J54,J78) &(I6,I29,I53,I77)...
This should probably be done as a Macro since I have to do this 20 or more
times, but I'm not particularly adept at VBA.
Darrell
R
Roger Govier
Hi
I made the references absolute in my posting (I also made a mistake with
the last one, saying J57 instead of J77).
as I had thought that it would just be those fixed 4 cells.
If you remove the $ signs to make the formula relative, then it can be
copied to other cells as you describe.
=AND(J6<>"",J6=MIN(J6,J29,J53,J77))
The easiest way, is to use the Format painter. Place your cursor in cell
J6, click on the Format Painter ( paintbrush icon on Toolbar) and
"paint" the format to any other cells you want.
I made the references absolute in my posting (I also made a mistake with
the last one, saying J57 instead of J77).
as I had thought that it would just be those fixed 4 cells.
If you remove the $ signs to make the formula relative, then it can be
copied to other cells as you describe.
=AND(J6<>"",J6=MIN(J6,J29,J53,J77))
The easiest way, is to use the Format painter. Place your cursor in cell
J6, click on the Format Painter ( paintbrush icon on Toolbar) and
"paint" the format to any other cells you want.
D
Dr. Darrell
Roger:
Once again, Thank you.
I have duplicated the Conditional Formating using the MAX Function, It works
good as well.
I have room for 1 more Conditional Format. Can I easily identify the next to
the lowest value in these cells?
Darrell
Once again, Thank you.
I have duplicated the Conditional Formating using the MAX Function, It works
good as well.
I have room for 1 more Conditional Format. Can I easily identify the next to
the lowest value in these cells?
Darrell
R
Roger Govier
Take a look at the Small and Large functions
=SMALL((J6,J29,J53,J77),2)
would return the second smallest of values in J6, J29, J53 and J77
=Large(A1:A20,3)
would return the 3rd largest of the range A1 to A20
=SMALL((J6,J29,J53,J77),2)
would return the second smallest of values in J6, J29, J53 and J77
=Large(A1:A20,3)
would return the 3rd largest of the range A1 to A20