Conditional Formatting

Chorususer

New Member
Joined
Jul 15, 2008
Messages
28
<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=1549><TBODY><TR><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
Average​
</TD><TD height=16 width="4%">
No.Rounds​
</TD></TR><TR><TD height=16 width="4%">
</TD><TD height=16 width="4%">
1​
</TD><TD height=16 width="4%">
2​
</TD><TD height=16 width="4%">
3​
</TD><TD height=16 width="4%">
4​
</TD><TD height=16 width="4%">
5​
</TD><TD height=16 width="4%">
6​
</TD><TD height=16 width="4%">
7​
</TD><TD height=16 width="4%">
8​
</TD><TD height=16 width="4%">
9​
</TD><TD height=16 width="4%">
10​
</TD><TD height=16 width="4%">
11​
</TD><TD height=16 width="4%">
12​
</TD><TD height=16 width="4%">
13​
</TD><TD height=16 width="4%">
14​
</TD><TD height=16 width="4%">
15​
</TD><TD height=16 width="4%">
16​
</TD><TD height=16 width="4%">
17​
</TD><TD height=16 width="4%">
18​
</TD><TD height=16 width="4%">
19​
</TD><TD height=16 width="4%">
20​
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD></TR><TR><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD></TR><TR><TD height=16 width="4%">
Bevan​
</TD><TD height=16 width="4%">
70​
</TD><TD height=16 width="4%">
65​
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
70​
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
65​
</TD><TD height=16 width="4%">
70​
</TD><TD height=16 width="4%">
71​
</TD><TD height=16 width="4%">
72​
</TD><TD height=16 width="4%">
73​
</TD><TD height=16 width="4%">
71​
</TD><TD height=16 width="4%">
74​
</TD><TD height=16 width="4%">
80​
</TD><TD height=16 width="4%">
58​
</TD><TD height=16 width="4%">
70​
</TD><TD height=16 width="4%">
85​
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
68.20​
</TD><TD height=16 width="4%">
14​
</TD></TR><TR><TD height=16 width="4%">
Sean​
</TD><TD height=16 width="4%">
71​
</TD><TD height=16 width="4%">
68​
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
70​
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
62​
</TD><TD height=16 width="4%">
70​
</TD><TD height=16 width="4%">
71​
</TD><TD height=16 width="4%">
72​
</TD><TD height=16 width="4%">
71​
</TD><TD height=16 width="4%">
68​
</TD><TD height=16 width="4%">
75​
</TD><TD height=16 width="4%">
80​
</TD><TD height=16 width="4%">
57​
</TD><TD height=16 width="4%">
70​
</TD><TD height=16 width="4%">
85​
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
</TD><TD height=16 width="4%">
67.80​
</TD><TD height=16 width="4%">
14​
</TD></TR></TBODY></TABLE>

In the above sheet I want to high light per person their lowest scores, once they go over 11 scores entered I want to able to highlight their best 10 only. There will be blank cells along each row as not every week is a score entered.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

I'm not 100% sure this will work for your top 10....
In 2007 Conditional formatting-New Rule-Use a formula etc..
Paste this code into the white box then format to suit..

=AND(COUNT($B$5:$R$5)>11,B5>=LARGE($B$5:$R$5,10))

Try this for your lowest score

=MIN($B$5:$R$5)

Change your ranges to suit your sheet.

Ak
 

Chorususer

New Member
Joined
Jul 15, 2008
Messages
28
Thanks for your help, I still don't get ten scores highlighted. It may have something to do with some of the scores being the same and blank cells. Can you think of anything else?

Cell range is B2:U2 lowest scores highlighted in 20 cells but only up to maximum of 10 highlighted. Found formular to average these lowest 10 scores just want to be able to highlight the ones counted.
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

I get 10 scores Highlighted IF there are 10 unique numbers,
I also get more than 10 scores Highlighted IF there are duplicate scores.
Did you want to ignore the duplicated scores?

Try this for the 10 lowest scores.....

=AND(COUNT($B$2:$U$2)>11,B2<>"",B2<=SMALL($B$2:$U$2,10))

Ak
 

Chorususer

New Member
Joined
Jul 15, 2008
Messages
28

ADVERTISEMENT

Rank top 10 in Conditional Formating

I have this sheet below assume range is B4:U4.

What I want to do is use conditional formating to highlight 10 lowest scorces including duplicates. so if 11 70's only want first 10 highlighted.

Can anyone help please.

<TABLE dir=ltr border=1 cellSpacing=0 borderColor=#000000 cellPadding=2 width=1414><TBODY><TR><TD height=16 width="5%">
</TD><TD height=16 width="5%">
Score 1​
</TD><TD height=16 width="5%">
Score 2​
</TD><TD height=16 width="5%">
Score 3​
</TD><TD height=16 width="5%">
Score 4​
</TD><TD height=16 width="5%">
Score 5​
</TD><TD height=16 width="5%">
Score 6​
</TD><TD height=16 width="5%">
Score 7​
</TD><TD height=16 width="5%">
Score 8​
</TD><TD height=16 width="5%">
Score 9​
</TD><TD height=16 width="5%">
Score 10​
</TD><TD height=16 width="5%">
Score 11​
</TD><TD height=16 width="5%">
Score 12​
</TD><TD height=16 width="5%">
Score 13​
</TD><TD height=16 width="5%">
Score 14​
</TD><TD height=16 width="5%">
Score 15​
</TD><TD height=16 width="5%">
Score 16​
</TD><TD height=16 width="5%">
Score 17​
</TD><TD height=16 width="5%">
Score 18​
</TD><TD height=16 width="5%">
Score 19​
</TD><TD height=16 width="5%">
Score 20​
</TD></TR><TR><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD></TR><TR><TD height=16 width="5%">
Dave​
</TD><TD height=16 width="5%">
70​
</TD><TD height=16 width="5%">
70​
</TD><TD height=16 width="5%">
70​
</TD><TD height=16 width="5%">
75​
</TD><TD height=16 width="5%">
76​
</TD><TD height=16 width="5%">
70​
</TD><TD height=16 width="5%">
70​
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
75​
</TD><TD height=16 width="5%">
70​
</TD><TD height=16 width="5%">
73​
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
78​
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD></TR><TR><TD height=16 width="5%">
Ian​
</TD><TD height=16 width="5%">
80​
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
81​
</TD><TD height=16 width="5%">
72​
</TD><TD height=16 width="5%">
67​
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
69​
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
72​
</TD><TD height=16 width="5%">
75​
</TD><TD height=16 width="5%">
78​
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
67​
</TD><TD height=16 width="5%">
68​
</TD><TD height=16 width="5%">
80​
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD><TD height=16 width="5%">
</TD></TR></TBODY></TABLE>
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Re: Rank top 10 in Conditional Formating

using your posted data in cells A1:U4

This formula, used in conditional formatting, highlights the lowest 10 scores for Dave:
• Select B3:U4, with B3 as the active cell

For Excel 2003
• Format.Conditional_Formatting
...Formula is: =(RANK(B3,$B3:$U3,TRUE)+COUNTIF($B3:B3,B3)-1)<=10
...Click: Format...set the highlight color...Click: OK

For Excel 2007
• Home.Conditional_Formatting.New_rule
...Use a formula to determine which cells to format
...Formula: =(RANK(B3,$B3:$U3,TRUE)+COUNTIF($B3:B3,B3)-1)<=10
...Click: Format...set the highlight color...Click: OK

Is that something you can work with?
 

Chorususer

New Member
Joined
Jul 15, 2008
Messages
28
Re: Rank top 10 in Conditional Formating

Fantastic it works perfectly!!!!!

Thank you for your help.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,892
Messages
5,514,010
Members
408,981
Latest member
Slumsu

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top