Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    8 largest? + (office 2000)

    Hi, and thanks in advance. I'm not a very experienced user but have been presented with a problem. Any help would be appreciated. Trying to determine and add the "top" 8 golf scores for variety of competitors from a variety of ranges for each competitor. In addition, 4 scores must be included. All scores are entered into the spreadsheet with a row representing one competitor. Within the row are various ranges. We are trying to take the top 8 scores from the ranges and add them together, plus add in 4 specified scores (tournaments) that are included in the row, but can't be considered for the top 8 scores. We have tried using large, but don't know how to exclude the four required scores from the determination and then add them to the total of the eight. So is everybody confused. It probable sounds more complicated than it is, but we are lost. Any and all help is appreciated.
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 8 largest? + (office 2000)

    To help clear the confusion it would help if you could post a sample of your data and the expected results <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Peter

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: 8 largest? + (office 2000)

    Attached does what I think you want. It uses what's called an array formula to multiply each respective score by either a 1 or a zero. You would need to put a zero in the columns corresponding to the 4 scores that are always included (but are excluded from the top 8). After entering or modifying an array formula, you have to hold down the Ctrl and Shift keys before pressing Enter (rather than just hitting Enter). In my example I just did the top 3 with 2 scores excluded and added at the end.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: 8 largest? + (office 2000)

    Modified version of previous attachment. Rather than a very long formula adding MAX LARGE(,2) LARGE(,3) etc., I modified the array formula to sum the numbers that are >= LARGE(,8).
    Attached Files Attached Files

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: 8 largest? + (office 2000)

    Using Chip Person's "Top n" array formula, and using 4 Tournaments (T1:T4) and 12 Events (E1:E12), the attached takes the average of the top 8 Event Scores, and the average of the Tournament scores, and then the simple average of those two averages.

    BTW, I am defining "top" as lowest score! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Lounger
    Join Date
    Jul 2002
    Location
    Winnipeg, Manitoba
    Posts
    40
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: 8 largest? + (office 2000)

    Thanks to all that helped out. It is greatly appreciated. We have learned much from the wisdom that you shared. <img src=/S/thankyou.gif border=0 alt=thankyou width=36 height=24> Thanks again.
    E. Fred Schneider
    someone who knows just enough to get themselves into trouble
    Winnipeg, Canada
    fred.schneider@sportmanitoba.ca

Posting Permissions

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