Highlight a max number

M

mpenkala

hey gang,
easy one here (I'm assuming).

ColAO contains 18 numbers, from AO3-AO20. The numbers change constantly
while I input data. How can I make the current high number change color? I
know it with conditional formatting, but can't seem to get the formula
correct. Also - if 2 or more numbers are currently the highest, both should
be highlighted.

Thanks,
Matt
 
A

akphidelt

You should be able to get this to work with conditional formatting

Select the area go to conditional formatting

Use this

Cell value is "equal to"

Then type in the next box "=Max(AO3:AO20)"
 
G

Gary''s Student

Conditional format FormulaIs:

=AO3=MAX($AO$3:$AO$20) and copy this down from AO3 thru AO20

pick a nice distinctive background color.
 
A

akphidelt

Use Gary's Students example.

Mine example only works if you select the entire column like Max(AO:AO)
 
T

T. Valko

What version of Excel are you using?

In versions prior to Excel 2007...

Select the range AO3-AO20
Goto the menu Format>Conditional Formatting
Formula Is:

=AO3=MAX(AO$3:AO$20)

Note that if the *entire* range is empty the *entire* range will be
highlighted. To prevent that (if that's a possibility):

=AND(COUNT(AO$3:AO$20),AO3=MAX(AO$3:AO$20))

Click the format button
Select the style(s) desired
OK out
 
C

Chip Pearson

Matt,

You can use Conditional Formatting from the Format menu to do this.
Conditional Formatting is used to change the style of a cell (back color,
fore color, borders, etc) depending on the value of the cell or the result
of a formula.

Select AO3:AO20 and choose Conditional Formatting from the Format menu. In
that dialog, change "Cell Value Is" to "Formula Is" and enter the following
formula in the input box. You'll want to include the '$' characters as
shown:

=$AO3=MAX($AO$3:$AO$20)

Then, click the Format button on the dialog and choose the formatting for
that cell. If the formula returns TRUE (or any numeric value not equal to
0) the chosen formatting will be applied. If the formula returns FALSE or 0,
the formatting is not applied.

See http://www.cpearson.com/excel/cformatting.htm for more info about
Conditional Formatting.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
M

mpenkala

Excellent - thanks guys!
Matt

T. Valko said:
What version of Excel are you using?

In versions prior to Excel 2007...

Select the range AO3-AO20
Goto the menu Format>Conditional Formatting
Formula Is:

=AO3=MAX(AO$3:AO$20)

Note that if the *entire* range is empty the *entire* range will be
highlighted. To prevent that (if that's a possibility):

=AND(COUNT(AO$3:AO$20),AO3=MAX(AO$3:AO$20))

Click the format button
Select the style(s) desired
OK out
Click to expand...
 
S

spottkitty

I'm trying to do two conditional formats and not getting the results desired
in excel 2003.

The first rule seems to work fine, which is to check the cell value against
a goal cell and turn it bold if the cell is equal or greater than the goal

Formula is =B5>=$B$19

Then I am also trying to highlight the top values in the range, which
sometimes there are more than one with the top value.

Formula is =B5=MAX($B$5:$B$17)

Highlight yellow.

I then copied the formatting over to the other cells in the range. The
numbers over the goal show up bold, but I've got no highlights!

I'd very much appreciate help identifying what I need to do to get this to
work!

Thanks!
Barbara
 
D

David Biddulph

If you satisfy the first condition in CF, it won't go on to test any later
conditions.

If you want a combination of your two conditions, add that combination
=AND(B5>=$B$19,B5=MAX($B$5:$B$17))
as the FIRST condition in the list, with appropriate formatting, then
follow on with your other conditions, which will be tested if the earlier
condition is not satisfied.
 
J

Jon Peltier

If you turn cells bold by conditional formatting, that condition is
satisfied, and the next condition (maximum) is not tested, so you don't get
any maximum value highlighted. Reverse the order of the conditions, so that
the maximum is checked first, then the goal.

- Jon
 
Top