Solved

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

Posted on 2013-11-21
4
245 Views
Last Modified: 2013-11-21
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
Comment
Question by:BHR
  • 2
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39667006
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
 

Author Comment

by:BHR
ID: 39667224
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 39667247
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
 

Author Comment

by:BHR
ID: 39667586
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel IF formula 3 21
Data Copy 4 26
How to get populate a column with a string using excel formula 5 9
MIN per Month 8 24
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question