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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,920
Messages
5,834,375
Members
430,282
Latest member
MeredithD26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top