 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006conditional formatting help
I'm trying to setup conditional formatting in Excel and not having much luck. My criteria is where any cell from d3:d28 is greater than 45, than tell me the highest value from i3:28 and color the winning cell in "i" blue. If possible, instead of coloring the winning cell in "i" blue, I'd like to color the entire row it resides in. Thanks >My criteria is where any cell from d3:d28 is greater than 45, than tell me >the highest value from i3:28 and color the winning cell in "i" blue. Need some clarification. So, if D25 = 50 and I5 is "the highest value from i3:28" color cell I5 or "the entire row it resides in." Is the entire row A5:IV5? Biff > I'm trying to setup conditional formatting in Excel and not having much > luck. [quoted text clipped - 6 lines] > > Thanks This is for the conditional formatting part. In A3 let the condition be Formula is =AND(SUMIF($D$3:$D$28,">"&45)>0,$I3=MAX($I$3:$I$28)) Copy the format to the entire range. > I'm trying to setup conditional formatting in Excel and not having much > luck. [quoted text clipped - 6 lines] > > Thanks Joe, Select all rows 3 to 28 (or cells that you actually want shaded, so that cell A3 is the activecell), and use Format / CF... with a "Formula is" of =AND(MAX($D$3:$D$28)>45,$I3=MAX($I$3:$I$28)) Set your format for a fill of blue, and you're done. Of course, if there are two or more values that match then highest value in column A, they will all be colored. HTH, Bernie MS Excel MVP > I'm trying to setup conditional formatting in Excel and not having much > luck. [quoted text clipped - 6 lines] > > Thanks Bernie, In a nutshell, this is a spreadsheet with softball averages. Some of the people in Column D had 2 at bats, and bat .1000 in Column I. I want to filter out people that had less than 45 at bats and have the highest batting average for the person over 45 at bats. With the formula you gave me, it ended up selecting the person with 1 at bat that is batting .1000, but it did highlight the entire row. How can we change it so it filters out people with less than 45 at bats? > Joe, > [quoted text clipped - 21 lines] >> >> Thanks Joe, That is a more complicated situation, one that requires that you use another cell with a formula that returns the Maximum value for column I where column D of the same row is over 45. In cell I2, array enter (enter using Ctrl-Shift-Enter) the formula =MAX((D3:D28>45)*I3:I28) Actually, I would recommend that you use =MAX((D3:D28>I1)*I3:I28) and then enter the 45 into cell I1. That way, you can easily change the at-bat-level as the season progresses. Anyway, this formula will ignore the values in column I, even if they are the actual max, if column D from that row is less than 45 (or, the value in cell I1). Then, with a cell in row 3 the active cell, use this CF formula: =AND($D3>45,$I3=$I$2) HTH, Bernie MS Excel MVP > Bernie, > [quoted text clipped - 33 lines] >>> >>> Thanks Highlight your range Conditonal Formatting Formula Is: AND(D3>45,I3=MAX($I$3:$I$28) Format any color you like > I'm trying to setup conditional formatting in Excel and not having much > luck. [quoted text clipped - 6 lines] > > Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.