Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    New Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    3 Star Lounger
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

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

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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    New Lounger
    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 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. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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 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. #13
    3 Star Lounger
    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 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.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  14. #14
    WS Lounge VIP sdckapr's Avatar
    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

  15. #15
    3 Star Lounger
    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 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.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Page 1 of 2 12 LastLast

Posting Permissions

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