Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Summing top 5 of 7 values

  1. #11
    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 have sent the file. The first failure is in the first line. The values for the cells in questions are:

    50 48 Blank 49 47 45 46

    The formula calculates this as 239 instead of 240.

    The next error occurs on the 6th row:

    Blank 45 48 44 43 38 41

    The formula calculates this as 218 instead of 221.

    It works properly for all remaining rows in the top section of the spreadsheet.

    The formula works well with >2 or >5 used in the first argument. It also works well using either >2 or >5 for rows that contain a value in only one cell and the rest blank.

    In reviewing the table further, the formula only seems to fail when there is only one blank cell in the row. If there are no blank cells, it works well and if there are 2 or more blank cells it works well.

    _________________
    Bob, feel free to E-Mail me
    Also, check out the Club Home Page or la Page d'acceuil du club

    [ This Message was edited by: Team BoMart on 2002-02-19 08:48 ]

  2. #12
    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 08:41, Team BoMart wrote:
    I have sent the file. The first failure is in the first line. The values for the cells in questions are:

    50 48 Blank 49 47 45 46

    The formula calculates this as 239 instead of 240.

    The next error occurs on the 6th row:

    Blank 45 48 44 43 38 41

    The formula calculates this as 218 instead of 221.

    It works properly for all remaining rows in the top section of the spreadsheet.

    The formula works well with >2 or >5 used in the first argument. It also works well using either >2 or >5 for rows that contain a value in only one cell and the rest blank.

    In reviewing the table further, the formula only seems to fail when there is only one blank cell in the row. If there are no blank cells, it works well and if there are 2 or more blank cells it works well.

    _________________
    Bob, feel free to E-Mail me
    Also, check out the Club Home Page or la Page d'acceuil du club

    [ This Message was edited by: Team BoMart on 2002-02-19 08:48 ]
    I'll defer to some else to make it pretty, but
    =IF(COUNT(A1:G1)<=5,SUM(A1:G1),IF(COUNT(A1:G1)=6,SUM(A1:G1)-MIN(A1:G1),SUM(A1:G1)-MIN(A1:G1)-SMALL(A1:G1,2)))

    should address your needs.

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

    Default

    That works very well!
    I think I can figure this one out as well:

    If there are 5 or less cells with values then sum the cells.

    Else If there are 6 cells with values then sum the cells and subtract the smallest value.

    Else Sum the cells, subtract the minimum value and subtract the second smallest value.

    Is that correct?

    Thanks for all of your help.

  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-19 08:53, IML wrote:
    On 2002-02-19 08:41, Team BoMart wrote:
    I have sent the file. The first failure is in the first line. The values for the cells in questions are:

    50 48 Blank 49 47 45 46

    The formula calculates this as 239 instead of 240.

    The next error occurs on the 6th row:

    Blank 45 48 44 43 38 41

    The formula calculates this as 218 instead of 221.

    It works properly for all remaining rows in the top section of the spreadsheet.

    The formula works well with >2 or >5 used in the first argument. It also works well using either >2 or >5 for rows that contain a value in only one cell and the rest blank.

    In reviewing the table further, the formula only seems to fail when there is only one blank cell in the row. If there are no blank cells, it works well and if there are 2 or more blank cells it works well.

    _________________
    Bob, feel free to E-Mail me
    Also, check out the Club Home Page or la Page d'acceuil du club

    [ This Message was edited by: Team BoMart on 2002-02-19 08:48 ]
    I'll defer to some else to make it pretty, but
    =IF(COUNT(A1:G1)<=5,SUM(A1:G1),IF(COUNT(A1:G1)=6,SUM(A1:G1)-MIN(A1:G1),SUM(A1:G1)-MIN(A1:G1)-SMALL(A1:G1,2)))

    should address your needs.
    Ian,

    I didn't follow the question closely, but wouldn't

    =IF(COUNT(A1:G1)>=5,SUMPRODUCT(LARGE(A1:G1,{1,2,3,4,5})),"Not Enough Data")

    suffice?

    Aladin

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

    Default

    Almost perfect. I started on the wrong path and kept going. To meet the critera, I think a simple
    =IF(COUNT(A1:G1)>5,SUMPRODUCT(LARGE(A1:G1,{1,2,3,4,5})),SUM(A1:G1))

    will do the trick. Much cleaner.

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

    Default

    Sorry for the delay, I was away.
    That works just great! And it is super clean!
    I guess I have allot to learn!! Thank you all for your assistance.

  7. #17
    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 have a question:

    Why do you use "SUMPRODUCT" rather than just "SUM"? "SUM" does seem to work.

  8. #18
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-20 05:46, Team BoMart wrote:
    I have a question:

    Why do you use "SUMPRODUCT" rather than just "SUM"? "SUM" does seem to work.
    You're absolutely right: LARGE produces an array of values that SUM can total, so SUMPRODUCT is not necessary to force LARGE (that was the idea behind its 'blind' use) to compute an array of values.

    And, thanks pointing that out.

    Aladin

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

    Default

    Thanks!

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
  •