conditional formatting

[FreakyGirl]

hello. love the forum - have found many answers here before but im truely stumped now.
what im trying to do is use conditional formatting to highlight ONLY the top 6 games for each player. until they have played 6 games nothing will be hightlighted. then once 6 games have been reached all 6 will highlight & as i enter more game scores the hightlighting needs to move accordingly. in respect to breaking ties, the earlier date value should be the one thats highlighted. i have used several diff codes that are supposed to do this but they all do something diff than what i need. there are 100 players in total & i need to do this on each row for each player.
consider the following:
column a1 - rank
column b1 - total of only the top 6 games highlighted
column c1 - player name
column d1 - player average
column e1 - total games played
column f1 to q1 - scores made on one of the dates of 12 games played (every sun, wed & fri in july(except for july 4th) - earlier dates listed 1st)
if a space is blank they did not play on that date. the point values can only vary from 0-8 on any date

formula 1: =F2>=LARGE($F2:$Q2,6) top 6 is: 8 6 5 5 4 4 but all the 4s are highlighted because of ties. i only want 6
formula 2: =RANK(F2,F2:Q2)+COUNTIF(F2:F2,F2)-1<=6 all of them are highlighted
formula 3: =RANK(F2,F$2:Q$2,1)+COUNTIF(F$2:F$2,F2)-1>=6 only highlights the 5 6 & 8
formula 4: =COUNTIF($F2:$Q2,"<"&F2)+COUNTIF($F2:$F2,F2)>=6 only highlights the 5 5 6 8
formula 5: =RANK(F2,F$2:Q$2)+COUNTIF(F$2:F$2,F2)-1<=6 highlights them all but the 1st 3
i really dont know what else to try & my excel knowledge is limited & self taught by using examples. i would appreciate any help on this. im using excel 2000

RANK    TOP 6 POINTS    PLAYER    AVERAGE    GAMES PLAYED    1-Jul    6-Jul    9-Jul    11-Jul    13-Jul    16-Jul    18-Jul    20-Jul    23-Jul    25-Jul    27-Jul    30-Jul
4    32    june    1.454545455    22    5    3    4    6    8        4    0    5    3    4    2

thanks in advance
FreakyGirl

 

[NARAYANK991]

Hi ,

Can you upload your workbook ?

Narayan

 

[FreakyGirl]

sample file attached. thanks so much.

 

[Somendra Misra]

Hi,

See your file.

Regards,

 

[FreakyGirl]

hello ty for the quick reply. however, it still shows ties as being highlighted.

column S shows the numbers that should be highlighted only so that i can see at a glance which ones they should be but that column is only a helper.

 

[Hui]

Use this formula for the CF:

=AND(SUMPRODUCT(--ISNUMBER($F2:$Q2))>=6,RANK(F2,$F2:$Q2)+SUMPRODUCT((F2=$F2:$Q2)*(F$1<$F$1:$Q$1))<7)

This will select the highest 6 values going from Right to Left (In order of the Dates in Row 1)

If you want the first 6 values going Left to Right change the formula to:
=AND(SUMPRODUCT(--ISNUMBER($F2:$Q2))>=6,RANK(F2,$F2:$Q2)+SUMPRODUCT((F2=$F2:$Q2)*(F$1>$F$1:$Q$1))<7)


See attached file:

 

[FreakyGirl]

hello

thanks so much!! LOL i def wouldnt have gotton that code on my own. Just one additional thing please... If i wanted to change the top 6 scores to top 7 scores do i change the =6 or <7?

thanks again for your time & effort into helping others become awesome
FreakyGirl

 

[Hui]

Both

 

[FreakyGirl]

hello again
you have been a great help. thanks for the quick replies also.
if i may bother you im having another issue...
There are 12 places to imput numbers. however i only need the 1st 6 entered counted. they may or may not be the highest so i cant use LARGE. i cant do SUM(G2:R2) because of possible blank spaces. the bigger picture here is that if i could get ISERROR to work properly i wouldnt need to do the above. However i read that using the ERROR could mean incompatability with later versions of Excel. Since i have 2000 i tried a diff route.
attached is my workbook that has since been updated with the following:
A player can play up to 12 times a month but only the top 6 scores are counted, averaged & ranked. however until those 6 scores are entered the sheet has #NUM in the approiate places. So by placing the above issue in column W of ONLY the 1st 6 scores entered i have a conditional to make the font of the #NUM white. it works, but only if the 1st 6 columns contain numbers as shown on A2. but same code in A3 because of blank spaces does not. The #NUM in columns A, B & C are the only ones im trying to 'hide'
again, i appreciate your time in helping beginners like me become awesome at excel.
FreakyGirl

 

[Hui]

Hi FreakyGirl

A Couple of things

Please start new posts for new questions. Often others won't visit a post which is being worked on by someone else and so you risk not being seen by potential solutions

Secondly is that after reading your question several times, I am still unsure what your question/problem is

I'd suggest using ranges rather than simply saying there are 12 area to enter numbers ?
State what the problem is clearly and succinctly use examples of where it doesn't work. Don't worry about why it doesn't work, as we will work that out once we know what the problem is

 

[FreakyGirl]

hello

Your right! Sorry about that. I will rephrase my question using ranges & put it on another thread.

have a great day
FreakyGirl