# Thread: excel formula needed (excel XP)

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

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

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

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

5. ## Re: excel formula needed (excel XP)

Steve:

Nice improvement. Now, why didn't I think of that!

Tony.

6. ## Re: excel formula needed (excel XP)

I did like your RANK over my LARGE suggestion. It does seem a little cleaner.

Steve

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

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

9. ## 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
Formula is:
=RANK(\$B3,\$B\$3:\$B\$8)=2
<Format> patterns - choose "Blue" color
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

10. ## 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 point-score or scores, and GREEN for 3rd highest point-score 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

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

12. ## Re: excel formula needed (excel XP)

Have you tried it? This scheme will highlight all as you ask.
If there is a 3-way (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 3-way 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)

13. ## Re: excel formula needed (excel XP)

Sylvie:

The problem with Rank (and Large), as you have probably discovered, is that if you have two equal-second 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 (Z-A).

Tony.

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

15. ## 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 point-score or scores, and GREEN for 3rd highest point-score 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.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•