+ Reply to Thread
Results 1 to 10 of 10

Adding the two lowest scores of 3 columns omitting zero

  1. #1
    Registered User
    Join Date
    02-21-2022
    Location
    State College, PA
    MS-Off Ver
    OFFICE 2019
    Posts
    2

    Adding the two lowest scores of 3 columns omitting zero

    Hello, first, I apologize for probably this easy question. I am starting to use excel more and more. But, this is a scenario on the personal side.

    My daughter is a skier and the cup standings take the sum of the two lowest times out of a possible 3 times. Initially all of the competitors were doing all 3 runs. They MUST complete two to be considered in the overall standings. Previously, I used the following formula to calculate the sum of the two lowest times.

    =SUM(SMALL(B61:D61,{1,2}))

    However, this late in the season, some racers are focused on just winning the day, so they only score once of the 3 possible runs. This is considered for the best time (lowest/fastest) for ONLY THAT DAY. But, for the overall Cup victory, assuming that the skier has completed the mandatory two runs out of 3 in each of the 3 races, they add the two together for the sum of their two lowest times.

    THE QUESTION

    PART 1: How do I add the two lowest scores in the range (B106:D106) and exclude any blank fields or zeros? I can go in an enter zeros for the kids that did not compete or only did 1 run if that is easiest.
    PART 2: How do I add that result to a running number from a previous range (B61,D61) and (B17,D17) [3 different races]
    PART 3: How do I rank the final number from PART 2 above from lowest to highest.

    I struggle nesting the different functions and I always get errors saying that I have exceeded the number of functions or syntax or something about using a apostrophe instead of equal sign.

    My 10 year old daughter and I thank you in advance!!!
    Last edited by ronjovi001; 02-22-2022 at 04:19 AM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Adding the two lowest scores of 3 columns omitting zero

    Hi
    I think it would be best if you attach the file or a sample file so your indication will be against something visual rather than abstract.
    Look at the yellow banner at the top of page for more instructions.

  3. #3
    Registered User
    Join Date
    02-21-2022
    Location
    State College, PA
    MS-Off Ver
    OFFICE 2019
    Posts
    2
    Sorry. Here is an attachment. Although the cell numbers are different on this sheet, I'm still trying to get the calcs to work.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Adding the two lowest scores of 3 columns omitting zero

    Where will I find your mocked up expected results?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Adding the two lowest scores of 3 columns omitting zero

    Not only are there no expected results... there are no data at all in the file!!
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Adding the two lowest scores of 3 columns omitting zero

    Scroll up, Glenn.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Adding the two lowest scores of 3 columns omitting zero

    Oh... pooh!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Adding the two lowest scores of 3 columns omitting zero

    To be honest, that does not help. The description does not match the file. You need to make sure the description relates to your file DIRECTLY.

    Do you get Dragon's Den in USA?

    You have 30-60 seconds to get my attention. If you haven't made it clear enough for me to understand the principle of what you want... I'm out and I move on.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Adding the two lowest scores of 3 columns omitting zero

    They do, I believe, and it's called 'Sharks' Tank'.

  10. #10
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Adding the two lowest scores of 3 columns omitting zero

    PART 1: How do I add the two lowest scores in the range (B106:D106) and exclude any blank fields or zeros? I can go in an enter zeros for the kids that did not compete or only did 1 run if that is easiest.
    PART 2: How do I add that result to a running number from a previous range (B61,D61) and (B17,D17) [3 different races]
    PART 3: How do I rank the final number from PART 2 above from lowest to highest.
    Hi,
    For part 1: Though i don't know where you want to place it in your file - here is the way to exclude zeros when you are searching for the smallest value,
    put this formula in AB16 to find the 2 smallest values in G16-I16:

    =AGGREGATE(15,6,(($G$16:$I$16)/($G$16:$I$16<>0)),1)+AGGREGATE(15,6,(($G$16:$I$16)/($G$16:$I$16<>0)),2)

    Part 2:
    just change the range in your formula to $N$16:$P$16, and add it to the previous one.

    =AGGREGATE(15,6,(($N$16:$P$16)/($N$16:$P$16<>0)),1)+AGGREGATE(15,6,(($N$16:$P$16)/($N$16:$P$16<>0)),2)

    Part 3:
    Use the function called "rank"

    Hope this helps...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting with highest / Lowest Scores
    By CallumWebley in forum Excel General
    Replies: 2
    Last Post: 09-07-2016, 03:59 AM
  2. Replies: 8
    Last Post: 12-20-2014, 04:37 PM
  3. average of lowest 3 of 5 scores >0
    By barnowl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2010, 08:59 PM
  4. Names and scores of the two lowest scores
    By dmyoungsal in forum Excel General
    Replies: 3
    Last Post: 07-15-2008, 11:47 AM
  5. Total Lowest Scores
    By dmyoungsal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-20-2007, 05:20 PM
  6. Average of lowest scores
    By Sem_Tex in forum Excel General
    Replies: 11
    Last Post: 12-19-2006, 10:33 AM
  7. [SOLVED] how to pick the lowest 10 golf scores
    By David Hurwitz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1