Golf league handicapping conundrum
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Golf league handicapping conundrum

  1. #1
    New Member
    Join Date
    Jan 2006
    Location
    Pennsylvania
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Golf league handicapping conundrum

    I built a golf handicapping sheet for my league of 30 players, each player has a row for score entries for 100+ rounds. Each week I enter into row A Frank's score, from A1 across. If Frank doesn't play that week, the entry is "0". Row B contains formulas in each cell from B1 across that look at A and either return "" for a "0", or a value under 50 to tenths, i.e. 1.7, 18.0, 0.6, -2.5, 10.4, 7.0, 22.8, 18.1, etc., depending on score in A. From C20 across, I need to find the ave of the minimum 10 values in B, but always from only the most recent 20 values in B.

    I have from MrExcel help last year:

    AVERAGE(SMALL($A1:AC1,1),SMALL($A1:AC1,2),SMALL($A1:AC1,3),SMALL($A1:AC1,4),SMALL($A1:AC1,5),SMALL($A1:AC1,6),SMALL($A1: AC1,7),SMALL($A1:AC1,8),SMALL($A1:AC1,9),SMALL($A1:AC1,10)

    But this doesn't look at only the most recent 20 values from which to pull the 10 minimums to ave.

    I greatly appreciate any thoughts.

    Chris

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    6,264
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Golf league handicapping conundrum

    This is hard to comprehend exactly what you want, especially since you seem to be mixing up rows and columns. The cell references you cite don't jibe with your explanation. However, look at this and see if it's close:

    A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB
    2 32 38 41 41 35 35 38 41 42 45 36 37 39 41 42 36 33 38 44 43 33
    3 37.2 38.1 37.9 37.1 36.8 36.8 37.4
    Sheet1

    Worksheet Formulas
    Cell Formula
    U3 =AVERAGE(SMALL(A2:T2,{1,2,3,4,5,6,7,8,9,10}))


    Enter the formula in U3 and drag it right as needed.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Jan 2006
    Location
    Pennsylvania
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Golf league handicapping conundrum

    So sorry - feel free to call me an idiot. Was up all night working on this. Yes of course, as you assumed, I meant to say ROW 1 for scores, ROW 2 for values, and ROW 3 for ave of ROW 2. However, I also was unclear in my asking. I want to look at only the 20 most recent NON-NULL cell values in row 2 to average the minimum 10 of these 20. That essentially is where I am stuck.

    Your formula is almost it, but can it always look at only the last 20 non-null cells. Very grateful for your decoding my gibberish.

    Chris

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    6,264
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Golf league handicapping conundrum

    I think my formula does what you want. First, the SMALL function ignores non-numeric values, so it ignores the null cells. Second, as you drag the formula to the right, the range changes from A2:T2 to B2:U2 to C2:V2 etc., meaning it only looks at the 20 most recent columns.

    There is a possible issue. If there are more than 10 null cells in the 20 column range, you'll get a #NUM error. If that's an issue, tell me what you'd like to do: average what's there, leave the error?
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  5. #5
    New Member
    Join Date
    Jan 2006
    Location
    Pennsylvania
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Golf league handicapping conundrum

    Yes, thank you, I noticed your formula ignores the non-numeric values. That's a help. But I need to look at the 20 most recent non-null columns, not just the 20 most recent columns for the range. That's where I'm stuck - the ole dynamic range. The look back range must include only exactly 20 of the most recent numeric values across the row. Then ave the lowest 10 of them. Helper cells are OK with me.
    I tried all versions of formulas returning non-null cell values, but I kept getting stuck on the range problem. It needs to count 20 of those most recent numeric values.
    Thanks so much for your patience.

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    6,264
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Golf league handicapping conundrum

    How about this then:

    A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB
    2 32 38 41 41 35 35 38 41 50 42 45 36 37 39 41 42 36 33 38 44 43 36
    3 #NUM! #NUM! #NUM! #NUM! #NUM! 35.8 36.5 36.5 36.3
    Sheet1

    Array Formulas
    Cell Formula
    T3 {=AVERAGE(SMALL(INDEX($A2:T2
    ,LARGE(IF(ISNUMBER($A2:T2),COLUMN($A2:T2)),20)
    ):T2,{1,2,3,4,5,6,7,8,9,10}
    )
    )}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself




    This formula looks for non-null cells, and gets the column number of the 20th non-null cell from the right. If there are not 20 non-null cells, the formula returns an error. Then it creates a range using that cell as the starting point, and the current column cell as the end, and again extracts the 10 smallest values from that range and averages them.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    6,264
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Golf league handicapping conundrum

    I noodled around with a couple of other ways, here's one:

    T3: =AVERAGE(SMALL(INDEX($A2:T2,AGGREGATE(14,6,ISNUMBER($A2:T2)*COLUMN($A2:T2),20)):T2,{1,2,3,4,5,6,7,8,9,10}))

    This doesn't require the Control+Shift+Enter, and it doesn't return an error. If there are not 20 scores yet, it returns the average of the 10 lowest so far.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  8. #8
    New Member
    Join Date
    Jan 2006
    Location
    Pennsylvania
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Golf league handicapping conundrum

    Thank you, thank you, thank you, good sir Eric. I see that the first works beautifully, I will play around later with the second option also, once I get back to the sheet. I trust it works just fine too, no doubt. Plus, I like the no error returns. Success, and without anyone wryly suggesting I go and buy myself some golf league software. HA!

    (it's more fun doing myself and practicing formula creation, and learning from the best)

    Best regards,
    Chris

  9. #9
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    6,264
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Golf league handicapping conundrum

    Happy to help!

    Enjoy your golfing!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  10. #10
    New Member
    Join Date
    Jan 2006
    Location
    Pennsylvania
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Golf league handicapping conundrum

    Sorry to revisit this - thought you had it solved. But I am getting a quirky result from your formula, now that it is in practice.

    I have J267:AF267 as follows:

    23.5 | 23.5 | " " | 20.8 | 22.6 | 18.9 | " " | 14.3 | 17.1 | 18.9 | " " | 15.3 | 23.5 | 13.4 | 22.6 | 16.2 | 18 | 22.6 | 20.8 | 23.5 | 19.8 | 22.6 | 14.3

    All of these values are results of the same formula returning either a value to tenths, or " " weekly. Columns will accrue.

    In AF275 I put your formula =AVERAGE(SMALL(INDEX($J267:AF267,AGGREGATE(14,6,ISNUMBER($J267:AF267)*COLUMN($J267:AF267),20)):AF267,{1,2,3,4,5,6,7,8,9, 10}))

    It is returning 18.19. Yet when I copy data to a new sheet and try again, it returns 16.62, the correct result. When I copy it all again onto same sheet, it returns 18.19.

    For review, the intent of the formula is to look back to the 20 most recent NON-EMPTY cells, and return the average of the 10 lowest values of those 20. I can't seem to produce 18.19 in any way manually, so I don't know where to look. Thought it may have to do with formula results, but no, does the same with just values entered across.

    Any ideas are greatly appreciated!

    Chris

User Tag List

Tags for this Thread

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
  •  

 

DMCA.com