[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Conditional Formatting per row... but based on only SOME column values.

Trying to highlight the min & max values per row.  Like the solution posted in
http://www.experts-exchange.com/Q_22692271.html  which uses
=A1=MAX(1:1)
=A1=MIN(1:1)

However, I only want to format PART of each row.  That is, my data doesn't start in A1 and doesn't contain all columns.  

For example: In data below I only want the MIN and MAX highlighted for each row (starting with r18) AND only for values in cols E:I.  

                E             F              G              H             I               J                            
Row 17:   Team1     Team2      Team3     Team4    MyTeam   Avg Ht
Row 18:   46.8         66.0         30.8         36.6         30.0         108
Row 19:   18.2         20.0         15.4         14.6         45.0         115

I've tried several variations on the formula and just not finding it.  
TIA.
0
BHR
Asked:
BHR
  • 2
  • 2
1 Solution
 
barry houdiniCommented:
You should select your whole range and then base the formulas on the top left cell. So for your example select something like E18:I100.

Now apply conditional formatting formula like this

=E18=MAX($E18:$I18)

Note the $ signs - you need those

....or to make sure you don't format empty rows you can amend to this version

AND(E18<>"",E18=MAX($E18:$I18))

see attached

regards, barry
cf-example.xlsm
0
 
BHRAuthor Commented:
Yes.. I see.   The trick (that I wasn't getting) was making the formula itself row specific, but then applying it to the entire range.  Nice tweak to skip empty rows.

Any thoughts on how to skip formatting on rows where all values (in cols E:I) are the same?
0
 
barry houdiniCommented:
What if the Max value is duplicated 2 or 3 times - the current formula will format all of those, is that OK? If you want that but you don't want the max value highlighted when they are all the same you could check for lower values, e.g.

=AND(E18=MAX($E18:$I18),COUNTIF($E18:$I18,"<"&E18)>0)

[you don't need to check blanks]

regards, barry
0
 
BHRAuthor Commented:
Impressive.  I combined and tweaked it all a bit to create another conditional formatting for when ALL values are the same.

=AND(E18<>"",COUNTIF($E18:$I18,"="&E18)=5)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now