Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Summing top 5 of 7 values

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    We have a non profit club that races 1/10th scale racing cars. We have 7 races in each series and only count the top 5 point results. We post the results using an Excel spreadsheet with names down one collumn and points accumulated down corresponding collumns. You can check it out at:
    http://www.igs.net/~rbeaudet/NCMA/rc...er20012002.htm

    The question:
    How can I sum the top 5 values across a row of 7 numbers?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, two options.

    =SUM(LARGE($A$1:$G$1,{1,2,3,4,5}))

    or a slightly different one

    =SUM($A$1:$G$1)-SUM(SMALL(A1:G1,{1,2}))
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Juan Pablo G.:
    Thanks for the quick reply. The formula works very well.

    I now have another condition which affects the results. The points standings have cells that are blank. The first option formula you provided needs to see a number in at least 5 cells. The second formula you gave me does not consider a blank cell as 0.

    I tried defining all of the cells as number, no decimal places hoping the formula would interpret a blank cell as a 0 - that did not work.

    The easy solution would of course be to put 0s in all the blank cells. However, there is a difference between blank cells and cells with 0s. Blank cells indicate the racer did not show for the day. 0s in a cell usually means the racer was disqualified since our point system ensures every racer gets points for participating in a final race.

    Any other suggestions?

    By the way, your solution has given me many ideas for other points spreadsheets I work with - thanks again!

    _________________
    Bob

    r.beaudet@videotron.ca

    [ This Message was edited by: Team BoMart on 2002-02-18 18:47 ]

  4. #4
    Board Regular RichardS's Avatar
    Join Date
    Feb 2002
    Location
    Victoria, Australia
    Posts
    761
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Juan Pablo, this looks familiar. Who was the golf handicapper?
    Richard

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unless you have another suggestion, I have come up with this work around:

    1. Unclick "zero values" in /tools/options/view
    2. At the beginning of the season, enter 0s in all fields
    3. Enter points in the proper places as the season progresses.
    4. Enter DQ for disqualified drivers and hope no one gets disqualified more than twice. If they do, we can readjust the formula for those few cases.

    This also solves another problem I just encounterred with the new points series that started last Sunday - Until at least five cells have a numerical value, the formula results in the #NUM! error. By having all cells with 0s, the formula works well and since the "zero values" option is unchecked, the table looks clear.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ugly, but this should work
    =IF(COUNT(A1:G1)>2,SUM(A1:G1)-(MIN(A1:G1)*(COUNT(A1:G1)>6))-(SMALL(A1:G1,2)*(COUNT(A1:G1)>5)),SUM(A1:G1))

    good luck

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am getting some errors in some cases.

    I am not quite familiar enough with Excel to completely understand the logic. Shooting my best shot, this is what I come up with:

    Should the first statement not be =IF(COUNT(A1:G1)>5 to find the comditions where there are numbers in six or seven of the collumns? The last part of the IF statement (the "else") simply sums all of the values across the row and this is correct based on the condition that there are 5 or less cells with values.

    When the IF statement is satisfied, "then" there are values in at least six of the seven cells. So you sum all of the cells and then subtract the smallest value of all of the cells and then subtract another value, I assume the next smallest value. _ I just can't quite figure that part of the logic out. I think I am getting confused with the additional COUNT statements.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What data are using when you get errors? The true part of the if statement tested fine for me using boolean logic to subtract the lowest two if there were six or seven. The only error can if there were less than three numbers, thus the less than 2 part. Alternatively, you can see if there are five or less just add those. Mine was just kind of a retro fit to avoid an error if there were 0, 1, or 2 numbers the boolean logic couldn't handle.

  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can e-mail you the spreadsheet if you wish?

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-19 07:58, Team BoMart wrote:
    I can e-mail you the spreadsheet if you wish?
    Sure, if you wish. I can look at tonight from home. Or just give me one or two of the lines the formula failed on and I can try to trouble shoot. (ie) 1,blank,3,4,5,blank,7

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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