Conditional Formatting

[Chorususer]

<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.

 

[Akashwani]

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]

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]

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]

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]

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]

Re: Rank top 10 in Conditional Formating

Fantastic it works perfectly!!!!!

Thank you for your help.