Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
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)" "mpenkala" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
Conditional format FormulaIs:
=AO3=MAX($AO$3:$AO$20) and copy this down from AO3 thru AO20 pick a nice distinctive background color. -- Gary''s Student - gsnu200767 "mpenkala" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
Use Gary's Students example.
Mine example only works if you select the entire column like Max(AO:AO) "akphidelt" wrote: 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)" "mpenkala" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
What version of Excel are you using?
In versions prior to Excel 2007... Select the range AO3-AO20 Goto the menu FormatConditional 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 -- Biff Microsoft Excel MVP "mpenkala" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
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) "mpenkala" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
Excellent - thanks guys!
Matt "T. Valko" wrote: What version of Excel are you using? In versions prior to Excel 2007... Select the range AO3-AO20 Goto the menu FormatConditional 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 -- Biff Microsoft Excel MVP "mpenkala" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
You're welcome!
-- Biff Microsoft Excel MVP "mpenkala" wrote in message ... Excellent - thanks guys! Matt "T. Valko" wrote: What version of Excel are you using? In versions prior to Excel 2007... Select the range AO3-AO20 Goto the menu FormatConditional 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 -- Biff Microsoft Excel MVP "mpenkala" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
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 "Chip Pearson" wrote: 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) "mpenkala" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
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. -- David Biddulph "spottkitty" wrote in message ... 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 "Chip Pearson" wrote: 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) "mpenkala" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight a max number
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "spottkitty" wrote in message ... 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 "Chip Pearson" wrote: 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) "mpenkala" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight cells with certain number values | Excel Worksheet Functions | |||
Highlight highest / lowest number in a row | Excel Discussion (Misc queries) | |||
highlight largest number in a row | Excel Worksheet Functions | |||
How to highlight the smallest number other than zero | New Users to Excel | |||
Highlight lowest number | Excel Discussion (Misc queries) |