Results 1 to 15 of 23
Thread: excel formula needed (excel XP)

20031104, 09:24 #1
 Join Date
 Oct 2003
 Location
 Perth, Western Australia, Australia
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
excel formula needed (excel XP)
I need help with a formula in a spread sheet. Have list of players names in column A and list of weekly scores in Column B. Need formula to flag first three highest scores. Can anyone help. PLEASE.

20031104, 09:36 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel formula needed (excel XP)
You can use:
=$b1>=large($B$1:$B$100,3)
will be true for the top 3 scores. You could put it into a formula for conditional formatting to "highlight " col A/B or both as desired. Change range as appropriate
Steve

20031104, 10:08 #3
 Join Date
 Mar 2003
 Location
 Beddau, Mid Glamorgan, Wales
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel formula needed (excel XP)
Smylie:
The RANK function returns the size of a number relative to other numbers in a list.
Thus =OR(RANK(B1,$B$1:$B$26)=1,RANK(B1,$B$1:$B$26)=2,RA NK(B1,$B$1:$B$26)=3) will flag the three highest scores in the list B1:B26 when entered as a format in the conditional formatting dialog box.
See attached W/Sheet as an example.
Tony.Regards,
Tony
[s] [/s]
www.SylviArtist.com

20031104, 10:19 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel formula needed (excel XP)
If you use the conditional format:
=RANK($B1,$B$1:$B$26)<=3
It simplifies it and it can be used in both col A and B. No need for 2 formulas or using ORs. It also is more generic if you wanted to get top 5 or top 10 (for example) without all the extra work.
Steve

20031104, 10:28 #5
 Join Date
 Mar 2003
 Location
 Beddau, Mid Glamorgan, Wales
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel formula needed (excel XP)
Steve:
Nice improvement. Now, why didn't I think of that!
Tony.Regards,
Tony
[s] [/s]
www.SylviArtist.com

20031104, 11:05 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel formula needed (excel XP)
I did like your RANK over my LARGE suggestion. It does seem a little cleaner.
Steve

20031105, 09:09 #7
 Join Date
 Oct 2003
 Location
 Perth, Western Australia, Australia
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel formula needed (excel XP)
Hi Steve,
Have attached small version of the spread sheet I am working on. Maybe you will understand better what I am trying to achieve, I hope so anyway. I find it so frustrating to know what I want to do but not know the language to make it happen. Hope you can help me with some ideas anyway.
Cheers
Sylvie

20031105, 09:49 #8
 Join Date
 Oct 2003
 Location
 Perth, Western Australia, Australia
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel formula needed (excel XP)
Sorry Hans,
The last thing I want is to make you more confused than I feel lately. I am still on "learners plates" but will eventually find my way around. Promise.
Appreciate your patience more than you know.
Cheers
Sylvie

20031105, 10:22 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel formula needed (excel XP)
Unless you really need column C for other reasons, I would eliminate it, since it only seems to give the color of the leader.
Select A3:B8 and we will color these with cond formatting:
Format  conditional formatting
Formula is:
=RANK($B3,$B$3:$B$8)=1
<Format> patterns  choose red color
<Add>
Formula is:
=RANK($B3,$B$3:$B$8)=2
<Format> patterns  choose "Blue" color
<Add>
Formula is:
=RANK($B3,$B$3:$B$8)=3
<Format> patterns  choose Green color
<OK>
Now the appropriate rows will be colored to match your "Legend" in A12:A14
To get the values in B12:B14 enter:
B12:
=LARGE($B$3:$B$8,1)
B13:
=LARGE($B$3:$B$8,2)
B14:
=LARGE($B$3:$B$8,3)
What Tony and I both understood from your original question "Need formula to flag first three highest scores" was that you just wanted to highlight the top 3. You didn't specify that you wanted the top 1, top2, and top3 all individually. Our formulas highlighted the top 3 scores. The ones here are modifications to get each individually.
Steve

20031106, 02:29 #10
 Join Date
 Oct 2003
 Location
 Perth, Western Australia, Australia
 Posts
 18
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel formula needed (excel XP)
G'Day Hans,
Thanks for your reply. It is almost what I am after but, what if there are two players with the same amount of accumulated points? How do I highlight them both.
If I used the term "Fairest and Best" would it make more sense to you what I need to do perhaps.
What if I put it this way.
Ok. Each week, when I update the kids scores in the appropriate weekly scores columns, column B display each childs updated total accumulated score.
However, some weeks as many as 4 kids could have scored the same amount of accumulated points.
I would like the child OR childrens names, in column A, to be highlighted in RED if they have scored the most points, BLUE for 2nd highests pointscore or scores, and GREEN for 3rd highest pointscore or scores. Then we can see at a glance each week who are the leading players in the team.
I hope I have said all that correctly so you understand what I am trying to say.
I live for the day when I can confidently understand and use computer language.
Thanks heaps  Sylvie

20031106, 02:32 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: excel formula needed (excel XP)
<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17><!profile=sdckapr>sdckapr<!/profile>'s first name is Steve.

20031106, 10:15 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel formula needed (excel XP)
Have you tried it? This scheme will highlight all as you ask.
If there is a 3way (or more) tie for "1st" they will all be highlighted There is then no 2nd or 3rd.
if there is a 2way tie for 1st, they are both highlighted as 1st, and the next is THIRD place (there is no 2nd)
If there is a first and 2nd and 3way tie for 3rd, the first, 2nd, and 3 3rds will be highlighted.
If 4 have the same score and it is a 1, 2, 3rd place score they will be highlighted. But be careful of limitations of RANK and the "elimination" of places due to ties.
Steve
PS as Hans pointed out, I am Steve. (But, don't worry about it, Harriet)

20031106, 10:24 #13
 Join Date
 Mar 2003
 Location
 Beddau, Mid Glamorgan, Wales
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel formula needed (excel XP)
Sylvie:
The problem with Rank (and Large), as you have probably discovered, is that if you have two equalsecond scores, the next score is ranked fourth, not third.
However, looking at the problem in a different way, I don't immediately see from your S/sheet any particular order to the children's names. If this is the case, I would suggest the simplest way is to sort the table in descending order of scores, so that the leading scorers appear at the top of the list.
Your table, as it stands, can't be sorted because of the merged cells. I have attached an amended solution bringing the Week/Points heading into one cell. To show the leading players at the top of the list select B1(Score) and click on the Sort Descending button (ZA).
Tony.Regards,
Tony
[s] [/s]
www.SylviArtist.com

20031106, 10:39 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel formula needed (excel XP)
Tony,
I don't consider this a "problem" with rank or large. This is how it "should be".
3rd place implies that only 2 are better. If there is a tie for 2nd they are actually the 2nd and 3rd place scores. If you had 10 people compete and 9 tie for first, can the 10th person truly claim "2nd" place when 9 people were actually better. (I think not, he is 10th in rank, so he is in 10th place).
Steve

20031106, 10:57 #15
 Join Date
 Mar 2003
 Location
 Beddau, Mid Glamorgan, Wales
 Posts
 289
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel formula needed (excel XP)
Steve:
I agree entirely. However, this is not what Sylvie wants (so it's a 'problem' for her!). In her post above (310079) she says:
<font color=blue>I would like the child OR childrens names, in column A, to be highlighted in RED if they have scored the most points, BLUE for 2nd highests pointscore or scores, and GREEN for 3rd highest pointscore or scores.</font color=blue>
So if, say, three players score 12, four players score 11 and two players score 10 she wants to highlight them all. Can't think of a way using functions  no doubt you could Code it!
If Sylvie doesn't mind altering the list, Sorting seemed the easiest solution. Another possibilty is Advanced Filtering, but I am waiting for her response first.
Tony.Regards,
Tony
[s] [/s]
www.SylviArtist.com