+ Reply to Thread
Results 1 to 10 of 10

A Little golf help

  1. #1
    Registered User
    Join Date
    03-06-2015
    Location
    albuquerque
    MS-Off Ver
    10
    Posts
    3

    A Little golf help

    Every year we have a pretty big golf tournament in St George Utah. There are a lot of games going on during the 5 days of golf. One of which I am responsible for. We have a game where we track Birdies, Pars in a row, Par 3 total and Par 5 total. Each member that wants to enter in pays $10 for each pot. Of course we get all dominations. We are also given some seed money to add to the pot which comes in other denominations. So the spreadsheet we have has everyone's name down the left and there total winnings on the next column. The hardest part of this process is paying people out without not knowing what to put in each envelope until you get close to the end and notice you don't have enough small bills to distribute. So what I am looking for is a way to optimize the currencies so that it figures what to put into each envelop for each entry.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: A Little golf help

    Cross-posted at http://www.mrexcel.com/forum/excel-q...tribution.html
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: A Little golf help

    Quite likely there is no solution to your problem aside from getting lots of low denomination seed money and of course using the large denominations wherever you can.

    If you have enough low denomination seed money you should be OK statistically speaking and you probably know from experience about what that amount would be.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: A Little golf help

    If you just want to know what bills to put in what envelope (which seems more error-prone than just counting them in your sleep),

    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    2
    Denom >>
    $ -
    $ 100
    $ 50
    $ 20
    $ 10
    $ 5
    $ 1
    Total
    D2:J2: Input
    3
    Name
    Amt
    Qty >>
    4
    6
    36
    18
    12
    40
    $ 1,700
    K3 and down: =SUMPRODUCT($E$2:$J$2, $E3:$J3)
    4
    -
    -
    -
    -
    -
    -
    -
    D4:J4,D5:D30: =0
    5
    Alan
    $ 33
    -
    -
    -
    1
    1
    -
    3
    $ 33
    E5 and across and down: =MIN(INT(ROUND(($C5 - SUMPRODUCT($D$2:D$2, $D5:D5)), 2) / E$2), E$3 - SUM(E$4:E4))
    6
    Barb
    $ 139
    -
    1
    -
    1
    1
    1
    4
    $ 139
    7
    Cain
    $ 35
    -
    -
    -
    1
    1
    1
    -
    $ 35
    8
    Dana
    $ 69
    -
    -
    1
    -
    1
    1
    4
    $ 69
    9
    Eric
    $ 15
    -
    -
    -
    -
    1
    1
    -
    $ 15
    10
    Fran
    $ 39
    -
    -
    -
    1
    1
    1
    4
    $ 39
    11
    Gary
    $ 137
    -
    1
    -
    1
    1
    1
    2
    $ 137
    12
    Hana
    $ 78
    -
    -
    1
    1
    -
    1
    3
    $ 78
    13
    Ivan
    $ 81
    -
    -
    1
    1
    1
    -
    1
    $ 81
    14
    Jane
    $ 52
    -
    -
    1
    -
    -
    -
    2
    $ 52
    15
    Kent
    $ 16
    -
    -
    -
    -
    1
    1
    1
    $ 16
    16
    Leah
    $ 25
    -
    -
    -
    1
    -
    1
    -
    $ 25
    17
    Mark
    $ 128
    -
    1
    -
    1
    -
    1
    3
    $ 128
    18
    Nina
    $ 26
    -
    -
    -
    1
    -
    1
    1
    $ 26
    19
    Otto
    $ 202
    -
    1
    2
    -
    -
    -
    2
    $ 202
    20
    Peri
    $ 56
    -
    -
    -
    2
    1
    1
    1
    $ 56
    21
    Quin
    $ 41
    -
    -
    -
    2
    -
    -
    1
    $ 41
    22
    Rene
    $ 117
    -
    -
    -
    5
    1
    -
    7
    $ 117
    23
    Seth
    $ 49
    -
    -
    -
    2
    -
    -
    1
    $ 41
    24
    Tina
    $ 20
    -
    -
    -
    1
    -
    -
    -
    $ 20
    25
    Ulis
    $ 74
    -
    -
    -
    3
    1
    -
    -
    $ 70
    26
    Vera
    $ 49
    -
    -
    -
    2
    -
    -
    -
    $ 40
    27
    Wade
    $ 40
    -
    -
    -
    2
    -
    -
    -
    $ 40
    28
    Xana
    $ 22
    -
    -
    -
    1
    -
    -
    -
    $ 20
    29
    Yuri
    $ 80
    -
    -
    -
    4
    -
    -
    -
    $ 80
    30
    Zuni
    $ 77
    -
    -
    -
    2
    3
    -
    -
    $ 70
    31
    Total
    $ 1,700


    The red values are the values that get shorted because you don't have enough small bills.
    Last edited by shg; 09-29-2015 at 07:54 PM.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Little golf help

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    03-06-2015
    Location
    albuquerque
    MS-Off Ver
    10
    Posts
    3

    Re: A Little golf help

    Quote Originally Posted by shg View Post
    If you just want to know what bills to put in what envelope (which seems more error-prone than just counting them in your sleep),

    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    2
    Denom >>
    $ -
    $ 100
    $ 50
    $ 20
    $ 10
    $ 5
    $ 1
    Total
    D2:J2: Input
    3
    Name
    Amt
    Qty >>
    4
    6
    36
    18
    12
    40
    $ 1,700
    K3 and down: =SUMPRODUCT($E$2:$J$2, $E3:$J3)
    4
    -
    -
    -
    -
    -
    -
    -
    D4:J4,D5:D30: =0
    5
    Alan
    $ 33
    -
    -
    -
    1
    1
    -
    3
    $ 33
    E5 and across and down: =MIN(INT(ROUND(($C5 - SUMPRODUCT($D$2:D$2, $D5:D5)), 2) / E$2), E$3 - SUM(E$4:E4))
    6
    Barb
    $ 139
    -
    1
    -
    1
    1
    1
    4
    $ 139
    7
    Cain
    $ 35
    -
    -
    -
    1
    1
    1
    -
    $ 35
    8
    Dana
    $ 69
    -
    -
    1
    -
    1
    1
    4
    $ 69
    9
    Eric
    $ 15
    -
    -
    -
    -
    1
    1
    -
    $ 15
    10
    Fran
    $ 39
    -
    -
    -
    1
    1
    1
    4
    $ 39
    11
    Gary
    $ 137
    -
    1
    -
    1
    1
    1
    2
    $ 137
    12
    Hana
    $ 78
    -
    -
    1
    1
    -
    1
    3
    $ 78
    13
    Ivan
    $ 81
    -
    -
    1
    1
    1
    -
    1
    $ 81
    14
    Jane
    $ 52
    -
    -
    1
    -
    -
    -
    2
    $ 52
    15
    Kent
    $ 16
    -
    -
    -
    -
    1
    1
    1
    $ 16
    16
    Leah
    $ 25
    -
    -
    -
    1
    -
    1
    -
    $ 25
    17
    Mark
    $ 128
    -
    1
    -
    1
    -
    1
    3
    $ 128
    18
    Nina
    $ 26
    -
    -
    -
    1
    -
    1
    1
    $ 26
    19
    Otto
    $ 202
    -
    1
    2
    -
    -
    -
    2
    $ 202
    20
    Peri
    $ 56
    -
    -
    -
    2
    1
    1
    1
    $ 56
    21
    Quin
    $ 41
    -
    -
    -
    2
    -
    -
    1
    $ 41
    22
    Rene
    $ 117
    -
    -
    -
    5
    1
    -
    7
    $ 117
    23
    Seth
    $ 49
    -
    -
    -
    2
    -
    -
    1
    $ 41
    24
    Tina
    $ 20
    -
    -
    -
    1
    -
    -
    -
    $ 20
    25
    Ulis
    $ 74
    -
    -
    -
    3
    1
    -
    -
    $ 70
    26
    Vera
    $ 49
    -
    -
    -
    2
    -
    -
    -
    $ 40
    27
    Wade
    $ 40
    -
    -
    -
    2
    -
    -
    -
    $ 40
    28
    Xana
    $ 22
    -
    -
    -
    1
    -
    -
    -
    $ 20
    29
    Yuri
    $ 80
    -
    -
    -
    4
    -
    -
    -
    $ 80
    30
    Zuni
    $ 77
    -
    -
    -
    2
    3
    -
    -
    $ 70
    31
    Total
    $ 1,700


    The red values are the values that get shorted because you don't have enough small bills.
    That is perfect. What is the code behind it.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: A Little golf help

    There is no code, and the formulas are as shown.

  8. #8
    Registered User
    Join Date
    03-06-2015
    Location
    albuquerque
    MS-Off Ver
    10
    Posts
    3

    Re: A Little golf help

    Quote Originally Posted by shg View Post
    There is no code, and the formulas are as shown.
    I must be doing something wrong. I set it up exactly like you have it here and I get a value error

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: A Little golf help

    Attached. There's a UDF that generates random amounts in col C, but you need not enable macros -- just enter your own amounts.
    Attached Files Attached Files

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: A Little golf help

    Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

    If you are unclear about the request or instruction then send a private message to them asking for clarification.

    All participants:
    Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

+ 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. Golf spreadsheet help
    By titch66 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-15-2019, 05:27 PM
  2. Golf Workbook
    By rwhite713 in forum Excel General
    Replies: 5
    Last Post: 01-31-2015, 09:39 AM
  3. Golf pro that needs help
    By onin2golf in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 04-21-2014, 02:21 PM
  4. golf
    By johnandrews22 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-05-2013, 11:52 AM
  5. Golf handicap
    By DuncanP in forum Excel General
    Replies: 4
    Last Post: 03-22-2012, 11:48 AM
  6. Golf Handicap
    By dodger999 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-09-2009, 11:45 AM
  7. [SOLVED] golf score
    By Bob in forum Excel General
    Replies: 1
    Last Post: 07-11-2006, 01:35 PM
  8. golf handicap
    By neo314trinity in forum Excel General
    Replies: 5
    Last Post: 03-15-2006, 02:50 PM

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